Multiple rows with same fk to columns

Question

I need to write a t-sql query that transforms, transpose, whatever the right terminology a dataset as follow.

Table a
ID, CategoryID, Col1, Col2, Col3, Col4, Col5, Col6
1,     1,       val1, val2, val3, val4, val5, val6
2,     1,       val1, val2, val3, val4, val5, val6
3,     1,       val1, val2, val3, val4, val5, val6
4,     2,       val1, val2, val3, val4, val5, val6
5,     2,       val1, val2, val3, val4, val5, val6
6,     2,       val1, val2, val3, val4, val5, val6

TO

CategoryID, Col1, Col2, Col3, Col4, Col5, Col6, Col1, Col2, Col3, Col4, Col5, Col6, Col1, Col2, Col3, Col4, Col5, Col6
1, val1, val2, val3, val4, val5, val6, val1, val2, val3, val4, val5, val6, val1, val2, val3, val4, val5, val6
2, val1, val2, val3, val4, val5, val6, val1, val2, val3, val4, val5, val6, val1, val2, val3, val4, val5, val6

So for each categoryId in table a take all the columns. Make row with categoryId as primary key and place all column values after each other

Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Thom A, May 24 '20 at 16:09

score 1 · Accepted Answer · answered May 24 '20 at 16:20

If you know in advance the maximum number of rows per category, one option uses row_number() and conditional aggregation.

Here is how to do it for three columns with three possible rows per category

select
    categoryID,
    max(case when rn = 1 then col1 end) col1_1,
    max(case when rn = 1 then col2 end) col2_1,
    max(case when rn = 1 then col3 end) col3_1,
    max(case when rn = 2 then col1 end) col1_2,
    max(case when rn = 2 then col2 end) col2_2,
    max(case when rn = 2 then col3 end) col3_2,
    max(case when rn = 3 then col1 end) col1_3,
    max(case when rn = 3 then col2 end) col2_3,
    max(case when rn = 3 then col3 end) col3_3
from (
    select t.*, row_number() over(partition by categoryID order by id) rn
    from mytable t
) t
group by categoryID

Multiple rows with same fk to columns

1 Answers1