CODEWITHSUNDEEP
sqlgoogle-bigquery
AI52487963
AI52487963

Reputation: 1273

Doing a concat over a partition in SQL?

I have some data ordered like so:

date, uid, grouping
2018-01-01, 1, a
2018-01-02, 1, a
2018-01-03, 1, b
2018-02-01, 2, x
2018-02-05, 2, x
2018-02-01, 3, z
2018-03-01, 3, y
2018-03-02, 3, z

And I wanted a final form like:

uid, path
1, "a-a-b"
2, "x-x"
3, "z-y-z"

but running something like

select
a.uid
,concat(grouping) over (partition by date, uid) as path
from temp1 a

Doesn't seem to want to play well with SQL or Google BigQuery (which is the specific environment I'm working in). Is there an easy enough way to get the groupings concatenated that I'm missing? I imagine there's a way to brute force it by including a bunch of if-then statements as custom columns, then concatenating the result, but I'm sure that will be a lot messier. Any thoughts?

Upvotes: 3

Views: 2680

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You are looking for string_agg():

select a.uid, string_agg(grouping, '-' order by date) as path
from temp1 a
group by a.uid;

Upvotes: 4

Related Questions