TSQL Pivot data

Question

Data :

id  data
1   1
1   4
2   2
2   1
3   3
3   2
4   4
4   1
5   5
5   2

I need to pivot the above data so that I get the following result :

id  Col1   Col2
1   1        4
2   2        1
3   3        2
4   4        1
5   5        2

I am trying the following query but the columns are always Null :

DECLARE @TEMP TABLE (id int,data varchar(200))

INSERT INTO @TEMP VALUES (1,'1')
INSERT INTO @TEMP VALUES (1,'4')
INSERT INTO @TEMP VALUES (2,'2')
INSERT INTO @TEMP VALUES (2,'1')
INSERT INTO @TEMP VALUES (3,'3')
INSERT INTO @TEMP VALUES (4,'2')
INSERT INTO @TEMP VALUES (4,'4')
INSERT INTO @TEMP VALUES (4,'1')
INSERT INTO @TEMP VALUES (5,'5')
INSERT INTO @TEMP VALUES (5,'2')

SELECT * FROM @TEMP 
    PIVOT
    (
      MAX(Data)
      FOR Data IN ([Col1],[Col2])
    )
    AS p

RESULT:

id  Col1    Col2
1   NULL    NULL
2   NULL    NULL
3   NULL    NULL
4   NULL    NULL
5   NULL    NULL

Answer 1

The problem with your query is you don't have any values in your Data column which are named Col1 or Col2.

When you are writing a PIVOT query the critical part of the query is:

pivot
(
  max(data)  -- < these are the values that you need displayed in the new columns
  for col in (Col1, Col2)  -- < this is the new column names from existing values
) p

Your query is using Col1, Col2 but those aren't defined anywhere so you are returning null.

You currently don't have anything that could be used as the new column names. In order to get the result, you will want to use a windowing function similar to row_number to generate a unique sequence over each id. The basic syntax will be:

select id, Col1, Col2
from
(
  select id, data,
    col =
      'Col'+
        cast(row_number() over(partition by id
                                order by data) as varchar(2))
  from yourtable
) d
pivot
(
  max(data)
  for col in (Col1, Col2)
) p;

See SQL Fiddle with Demo. You'll notice that in the subquery I use the windowing function to create the new column names Col1, Col2, etc. This is then used in the pivot.

Now for your situation if you are going to have an unknown number of possible columns, then you will need to use dynamic SQL. This creates a SQL string that will then be executed:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col) 
                    from
                    (
                      select col =
                        'Col'+
                          cast(row_number() over(partition by id
                                                  order by data) as varchar(2))
                      from yourtable
                    ) d
                    group by Col
                    order by col
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ' 
            from 
            (
              select id, data,
                col =
                  ''Col''+
                    cast(row_number() over(partition by id
                                            order by data) as varchar(2))
              from yourtable
            ) x
            pivot 
            (
                max(Data)
                for col in (' + @cols + ')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo.

Finally, this could also be done without using PIVOT by using an aggregate function with a CASE expression:

select id, 
  Col1 = max(case when seq = 1 then data end), 
  Col2 = max(case when seq = 2 then data end)
from
(
  select id, data,
    seq = row_number() over(partition by id
                            order by data)
  from yourtable
) d
group by id;

See SQL Fiddle with Demo. All versions give the same result:

| ID | COL1 | COL2 |
|----|------|------|
|  1 |    1 |    4 |
|  2 |    1 |    2 |
|  3 |    2 |    3 |
|  4 |    1 |    4 |
|  5 |    2 |    5 |

Answer 2

You can do this without a PIVOT like so:

;WITH CTE AS   
(
 SELECT 
 *,
 ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) RN 
 FROM @TEMP)

SELECT DISTINCT t.id, c1.data, c2.data
FROM @temp t
INNER JOIN CTE c1 ON c1.id = t.id AND c1.rn = 1
INNER JOIN CTE c2 ON c2.id = t.id AND c2.rn = 2

Note that this will work in this specific case. However, you should try to understand @bluefeet's answer to get a thorough understanding of how PIVOT works and how you can use it if you had multiple columns in another scenario.