Transpose rows to columns in SQL Server

Question

is there a way to transpose this table:

A   a
A   b
A   c
A   d
B   e
B   f
C   g
C   h
C   i

to this?

A   B   C
a   e   g
b   f   h
c       i
d       

Many thanks!

Answer 1

You can do this using ROW_NUMBER and conditional aggregation:

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)
    FROM #tbl
)
SELECT
    A = MAX(CASE WHEN col1 = 'A' THEN col2 END),
    B = MAX(CASE WHEN col1 = 'B' THEN col2 END),
    C = MAX(CASE WHEN col1 = 'C' THEN col2 END)
FROM Cte
GROUP BY rn

ONLINE DEMO

Answer 2

Dynamic sql version for Felix Pamittan's answer

Query

DECLARE @sql AS VARCHAR(MAX);

SELECT @sql = 'WITH Cte AS(
                   SELECT *, rn = ROW_NUMBER() OVER(
                       PARTITION BY col1 ORDER BY col2
                   ) 
                   FROM your_table_name
                 ) SELECT ' 
+ STUFF((
    SELECT DISTINCT 
    ',COALESCE(MAX(CASE col1 WHEN ''' + col1 + ''' THEN col2 END), '''') AS [' + col1 + ']'
    FROM your_table_name
    FOR XML PATH('')), 1, 1, '');

SELECT @sql += ' FROM cte GROUP BY rn;';
EXEC(@sql);

Result

+---+---+---+
| A | B | C |
+---+---+---+
| a | e | g |
| b | f | h |
| c |   | i |
| d |   |   |
+---+---+---+

Find demo here