Pivoting row's to columns

Question

How to achieve the below?? Anyone help me out

col_1 col_2
A     1
B     1
C     1
B     2
C     4
A     2
A     6

Output:

A B C
1 1 1
2 2 4
6

Answer 1

This will do the job, but it seems like quite an odd thing to want to do, so I am probably missing something?

CREATE TABLE #table (col1 CHAR(1), col2 INT);
INSERT INTO #table SELECT 'A', 1;
INSERT INTO #table SELECT 'B', 1;
INSERT INTO #table SELECT 'C', 1;
INSERT INTO #table SELECT 'B', 2;
INSERT INTO #table SELECT 'C', 4;
INSERT INTO #table SELECT 'A', 2;
INSERT INTO #table SELECT 'A', 6;

WITH Ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) AS rank_id
    FROM
        #table),
Numbers AS (
    SELECT 1 AS number
    UNION ALL
    SELECT number + 1 FROM Numbers WHERE number < 50)
SELECT
    MAX(CASE WHEN col1 = 'A' THEN col2 END) AS [A],
    MAX(CASE WHEN col1 = 'B' THEN col2 END) AS [B],
    MAX(CASE WHEN col1 = 'C' THEN col2 END) AS [C]
FROM
    Numbers n
    INNER JOIN Ranked r ON r.rank_id = n.number
GROUP BY
    n.number;

Results are:

A   B   C
1   1   1
2   2   4
6   NULL    NULL

Answer 2

Looks like you are trying to pivot without aggregation? Here is another option:

select A, B, C from 
( select col1, col2, dense_rank() over (partition by col1 order by col2) dr from #table) t
pivot
( max(t.col2) for t.col1 in (A, B, C)) pvt;

Also check this out for more examples/discussion: TSQL Pivot without aggregate function