CODEWITHSUNDEEP
sql
Jason
Jason

Reputation: 31

SQL query - Cumulatively concatenate strings in consecutive rows

I'm a data analyst, so I write SQL queries to retrieve data from a database. I'm not sure what kind of SQL exactly, just assume the most standard (also not things like 'DECLARE @tbl', and no create functions etc.)

Here is my problem. Given the following table:

name number letter
A 1 a
A 2 b
A 3 c
A 4 d
B 1 a
B 2 b
B 3 c
B 4 d

I want the following result: (concatenate letter cumulatively, order by number))

name number letter result
A 1 a a
A 2 b a,b
A 3 c a,b,c
A 4 d a,b,c,d
B 1 a a
B 2 b a,b
B 3 c a,b,c
B 4 d a,b,c,d

Any help is highly appreciated. Thanks very much.

Upvotes: 0

Views: 177

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This answers the original version of the question which was tagged MySQL.

MySQL doesn't support group_concat() as a window function. So a subquery may be your best alternative:

select t.*,
       (select group_concat(t2.letter order by t2.number)
        from t t2
        where t2.name = t.name and t2.number <= t.number
       ) as letters
from t;

Upvotes: 1

Related Questions