BigQuery SQL syntax error FOR function ("Syntax error: Expected ")" but got keyword FOR at [7:18]")

Question

I needed to concatenate data in one column (cont_url) based on dev_id. I was able to successfully do this in MS-SQL with this query, but I need to do this in BigQuery.

select
    dev_id,
    stuff(
    (SELECT '|||' + cont_url
      FROM `test_table_sample`
        WHERE  dev_id = a.dev_id
         FOR XML PATH (''))                --Error is here
        , 1, 1, '') as testlist
from
    `test_table_sample` as a
group by
    dev_id

When I have the table mounted in Big Query and try to run the same query, I get a syntax error.

Expected ")" but got keyword FOR at [7:18]

I do not know what I am doing wrong and how is BigQuery standard SQL syntax different than T-SQL.

I have included a sample table of data.

test_table_sample
dev_id  cont_url
Device1 Link1
Device1 Link2
Device1 Link3
Device1 Link4
Device2 anotherLink1
Device2 anotherLink2
Device2 anotherLink3
Device2 anotherLink4
Device2 anotherLink5

Here are the results of the query.

Results 
dev_id  cont_url
Device1 Link1|||Link2|||Link3|||Link4
Device2 anotherLink1|||anotherLink2|||anotherLink3|||anotherLink4|||anotherLink5

Gordon Linoff · Accepted Answer · 2020-01-03 18:27:15Z

2

BigQuery allows you to just aggregate strings. The syntax is much simpler:

select dev_id, string_agg(cont_url, '|||') as testlist
from `test_table_sample` as a
group by dev_id;

That said, I strong advise you to use arrays instead:

select dev_id, array_agg(cont_url) as testlist
from `test_table_sample` as a
group by dev_id;

Much better than awkwardly delimited strings.

answered Jan 3, 2020 at 18:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Putnik Over a year ago

Hi, thank you very much for the answer. I guess there is no way to have the url data in one cell ( I will be counting the unique user paths and wanted all of the links together in one cell for each user)

Gordon Linoff Over a year ago

@Putnik . . . string_agg() does what you explicitly are doing in SQL Server. array_agg() is preferable probably.

rtenha Over a year ago

Do both if you are curious! They are using the same aggregation.

Collectives™ on Stack Overflow

BigQuery SQL syntax error FOR function ("Syntax error: Expected ")" but got keyword FOR at [7:18]")

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related