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?

In any case, when you deal with an unknown number of columns to transpose you need to use dynamic sql. — jpw
– jpw, Commented May 19, 2015 at 11:29

Taylor · Accepted Answer · 2015-05-19 17:29:47Z

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

Collectives™ on Stack Overflow

T-SQL multiple rows to columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related