How to PIVOT SQL table

Question

DECLARE @table TABLE
(
id   INT,
dsc  CHAR(30)
)


INSERT INTO @table VALUES (111, 'desc-111-01')
INSERT INTO @table VALUES (111, 'desc-111-02')
INSERT INTO @table VALUES (111, 'desc-111-03')

INSERT INTO @table VALUES (222, 'desc-222-01')
INSERT INTO @table VALUES (222, 'desc-222-02')
INSERT INTO @table VALUES (222, 'desc-222-03')

I need to pivot it this way

111 | DESC-111-01 DESC-111-02 DESC-111-03.....
222 | DESC-222-01 DESC-222-02 DESC-222-03.....

Thank you, BBDS

Answer 1

If your data is exactly as you have shown (I mean it follows the pattern of having 3 description per ID), then:

select id, 
max(case when rowNo = 1 then dsc else '' end) as dsc1,
max(case when rowNo = 2 then dsc else '' end) as dsc2,
max(case when rowNo = 3 then dsc else '' end) as dsc3
from  
(select id, dsc, 
  row_number() over (partition by id order by dsc) as rowNo from @table) tmp
group by id;

Here is the sample in SQLFiddle