DineshDB DineshDB - 1 year ago 77
SQL Question

How to Get row values as columns in SQL?

I have a table Test with two columns.

Id Value
1 A
1 B
1 C

I want to get the result like below,

Id Value1 Value2 value3
1 A B C

How can I done this in SQL Server.

Answer Source

This is a pivot, but you don't have a column for the pivoting. row_number() can provide that. I usually use conditional aggregations for this.

select id,
       max(case when seqnum = 1 then value end) as value1,
       max(case when seqnum = 2 then value end) as value2,
       max(case when seqnum = 3 then value end) as value3
from (select t.*,
             row_number() over (partition by id order by (select null)) as seqnum
      from t
     ) t
group by id;

Note that SQL tables represent unordered sets. So, there is no information about ordering and the values could be in any order. If a column does specify the ordering, then include that in the order by rather than select null.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download