T-SQL multiple rows to columns

Question

I have dataset like this;

data_id     col_id      data_text_value
----------- ----------- ---------------
1           1           first first
1           2           first second
1           3           first third
2           2           second second

Here is union query to create the dataset;

select 1 as data_id, 1 as col_id, 'first first' as data_text_value union all
select 1 as data_id, 2 as col_id, 'first second' as data_text_value union all
select 1 as data_id, 3 as col_id, 'first third' as data_text_value union all
select 2 as data_id, 2 as col_id, 'second second' as data_text_value

I want that dataset queried to have data_id grouped into single row with all col_id's in different columns;

with test as (
select 1 as data_id, 1 as col_id, 'first first' as data_text_value union all
select 1 as data_id, 2 as col_id, 'first second' as data_text_value union all
select 1 as data_id, 3 as col_id, 'first third' as data_text_value union all
select 2 as data_id, 2 as col_id, 'second second' as data_text_value
) 
select 
    distinct test.data_id,
    cid1_text,
    cid2_text,
    cid3_text
from 
    test 
    left outer join (select data_id, data_text_value as cid1_text from test where col_id=1) cid1 on test.data_id = cid1.data_id
    left outer join (select data_id, data_text_value as cid2_text from test where col_id=2) cid2 on test.data_id = cid2.data_id
    left outer join (select data_id, data_text_value as cid3_text from test where col_id=3) cid3 on test.data_id = cid3.data_id

Which results as I want;

data_id     col_id      data_text_value
----------- ----------- ---------------
1           1           first first
1           2           first second
1           3           first third
2           2           second second

But, is there a way to do this the way that if col_id with value 4 would be inserted to the table, I would get fourth text column without changing the query?

This may [help](http://stackoverflow.com/questions/30321217/sql-transpose-rows-to-undefined-number-of-columns) or [this](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server/15745076#15745076) — ughai, May 19 '15 at 11:18
In any case, when you deal with an unknown number of columns to transpose you need to use dynamic sql. — jpw, May 19 '15 at 11:29

score 2 · Accepted Answer · answered May 19 '15 at 17:29

Is this what you're looking for? It will work even if you insert more data. Your Table

with test as (
select 1 as data_id, 1 as col_id, 'first first' as data_text_value union all
select 1 as data_id, 2 as col_id, 'first second' as data_text_value union all
select 1 as data_id, 3 as col_id, 'first third' as data_text_value union all
select 2 as data_id, 2 as col_id, 'second second' as data_text_value
) 

SELECT * INTO tbl_test
FROM test

Actual Query

DECLARE @PivotCols VARCHAR(MAX);

SELECT @PivotCols = COALESCE(@PivotCols + ',','') + QUOTENAME(col_id)
FROM tbl_test
GROUP BY col_id
ORDER BY col_id

EXEC
(
'SELECT *
FROM tbl_test
PIVOT
(
    MAX(data_text_value) FOR col_id IN (' + @PivotCols + ')
) pvt
'

Results:

data_id     1             2             3
----------- ------------- ------------- -------------
1           first first   first second  first third
2           NULL          second second NULL

T-SQL multiple rows to columns

1 Answers1