blackdragone blackdragone - 6 months ago 10
SQL Question

SQL server query: only certain column values into row, iterating

I need to build a SQL query in SQL SERVER 10.50.1600.1. I have the following model situation:

id1 | value1
id1 | value2
id2 | value1
id3 | value1
id3 | value2
...


and would like to end up in a situation as

id1 | value1 | value2
id2 | value1 | null
id3 | value1 | value2
...


I only know that for each id[n] there are at most 4 values recorded.
EDIT: I know there are at most 4 values for each, but they could be anything. They can be any number included in [0, 9000] or a string (about 10 possibilities). My bad, I didn't explain well.

Answer

Below query will help you :-

declare @test table (id nvarchar(50), value nvarchar(50))
insert @test(id, value) values 
('id1', 'value1'), 
('id1', 'value2'),
('id1', 'value3'), 
('id1', 'value4'),
('id2', 'value1'),
('id3', 'value1'),
('id3', 'value2'),
('id3', 'value3');


select ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS ROWNUM,id,value into #t1 from @test

SELECT distinct id, 
(select value from #t1 b where b.id=a.id AND b.ROWNUM=1) AS Value1,
(select value from #t1 b where b.id=a.id AND b.ROWNUM=2) AS Value2,
(select value from #t1 b where b.id=a.id AND b.ROWNUM=3) AS Value3,
(select value from #t1 b where b.id=a.id AND b.ROWNUM=4) AS Value4
FROM #t1 a

drop table #t1

Output :-

id  Value1  Value2  Value3  Value4
id1 value1  value2  value3  value4
id2 value1  NULL    NULL    NULL
id3 value1  value2  value3  NULL