CODEWITHSUNDEEP
sqlgoogle-bigquery
Putnik
Putnik

Reputation: 45

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

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

Upvotes: 0

Views: 4456

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 2

Related Questions