Transposing a column of data in SQL

Question

In SQL my data output is

Agreement ID	ProductStatus
125	A
125	C
125	N

I want to see this instead as

Agreement ID	ProductStatus
125	A,C, N

OR

Agreement ID	ProductStatus1	ProductStatus2	ProductStatus3
125	A	C	N

I've tried a few simple pivots, but the values a, c & n CAN be different and random values each time. Can anyone help?

What version of SQL technology are you using? MSSQL, MYSQL, other? — Brad, Jan 13 '21 at 15:20

score 0 · Answer 1 · answered Jan 13 '21 at 15:25

You can use a group_concat function. Your query will be something like this

SELECT agreement_id, group_concat(Product_Status)
FrOM mytable
group by agreement_id

This is for MySQL, for other databases you can search for group_concat alternative function for that particular database. Seems like you are new to database. You can use this reference to learn more. https://www.mysqltutorial.org/basic-mysql-tutorial.aspx

score 0 · Answer 2 · answered Jan 13 '21 at 15:34

If you can get three values in different columns using conditional aggregation:

select agreementid,
       max(case when seqnum = 1 then ProductStatus end) as ProductStatus_1,
       max(case when seqnum = 2 then ProductStatus end) as ProductStatus_2,
       max(case when seqnum = 3 then ProductStatus end) as ProductStatus_3
from (select t.*,
             row_number() over (partition by agreementid order by agreementid) as seqnum
      from t
     ) t
group by agreementid;

The SQL standard for creating a list is:

select agreementid,
       list_agg(ProductStatus, ',') within group (order by productstatus) as productstatuses
from t
group by agreementid;

Many databases have different names for this function.

In both of these cases, the ordering of the columns or elements of the list are indeterminate. SQL table represent unordered sets (well, technically multisets) so there is no ordering unless a column specifies the ordering.

Transposing a column of data in SQL

2 Answers2