How do you use transpose the following table in SQL?

Question

How do I transpose my table this format in SQL?

This is the original column.

This is the transposed column.

Is this feasible in SQL?

Answer 1

If you don't mind NULL's in the resultset then you could also use a pivot for this.

Example snippet:

declare @T table (column1 int, column2 varchar(1));

insert  @T (column1, column2) values 
(1, 'a'), (1,'b'), (1,'c'), (1,'d'), 
(2, 'e'), (2,'f'), (2,'g'), (2,'h'),
(3, 'w'), (3,'e'),
(4, 'r'), (4,'t'), (4,'a'), (4,'s'), (4,'d'), (4,'f');

select *
from (
    select 
     column1,
     column2, 
     concat('c',row_number() over (partition by column1 order by (select 0))) as col
    from @T
) q
pivot (
 max(column2)
 for col in ([c1],[c2],[c3],[c4],[c5],[c6])
) pvt;

Result:

column1 c1   c2   c3   c4   c5   c6
------- ---- ---- ---- ---- ---- ----
1       a    b    c    d    NULL NULL
2       e    f    g    h    NULL NULL
3       w    e    NULL NULL NULL NULL
4       r    t    a    s    d    f

Disclaimer : A row_number that's ordered by nothing doesn't guarantee the same order in the result.