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

Question

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.

Possible duplicate of [How to return multiple values in one column (T-SQL)?](http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql) — Alex K, Jun 08 '16 at 13:43

score 2 · Answer 1 · answered Jun 08 '16 at 13:51

If you know the values in advance, and the values are unique per ID (as they seem to be from your question statement) it's fairly straightforward:

-- Setup
declare @a table (id nvarchar(50), value nvarchar(50))
insert @a(id, value) values 
    ('id1', 'value1'), 
    ('id1', 'value2'), 
    ('id2', 'value1'),
    ('id3', 'value1'),
    ('id3', 'value2')
;

SELECT id, 
    MAX(CASE value WHEN 'value1' THEN value END) AS value1,
    MAX(CASE value WHEN 'value2' THEN value END) AS value2,
    MAX(CASE value WHEN 'value3' THEN value END) AS value3,
    MAX(CASE value WHEN 'value4' THEN value END) AS value4
FROM @a
GROUP BY id

score 1 · Accepted Answer · answered Jun 08 '16 at 14:06

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

score 0 · Answer 3 · answered Jun 08 '16 at 14:03

Assumtion: TABLE: table_name(id_column, value_column). With each value of id_column there are at most 4 values of value_column

Then you could use this

WITH tmp as
(SELECT 
    *
    ,ROW_NUMBER() over (PARTITION BY id_column order by value_column) rwn
FROM 
    table_name
)
SELECT 
    id_column
    ,MAX(CASE WHEN rwn = 1 THEN value_column END) value1
    ,MAX(CASE WHEN rwn = 2 THEN value_column END) value2
    ,MAX(CASE WHEN rwn = 3 THEN value_column END) value3
    ,MAX(CASE WHEN rwn = 4 THEN value_column END) value4
FROM
    tmp
GROUP BY 
    id_column;

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

3 Answers3