3

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AI52487963
  • 1,253
  • 2
  • 17
  • 36

1 Answers1

4

You are looking for string_agg():

select a.uid, string_agg(grouping, '-' order by date) as path
from temp1 a
group by a.uid;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Perfect! This works exactly as I needed it to. Thanks for surfacing up the string_agg() function, this looks quite useful. – AI52487963 May 14 '18 at 19:59