Select column values as columns using PIVOT

Question

I have a scenario where I wish to display the column values(Val1) for each unique column value (Val2) as an individual column, with a max of 10 columns.

CREATE TABLE #TEMP1 (Val1 NVARCHAR(4), Val2 NVARCHAR(10));

insert into #Temp1 Values ('S01','00731')
insert into #Temp1 Values ('S02','00731')
insert into #Temp1 Values ('S03','00731')
insert into #Temp1 Values ('S04','00731')
insert into #Temp1 Values ('S05','00731')
insert into #Temp1 Values ('S06','00731')
insert into #Temp1 Values ('S07','00731')
insert into #Temp1 Values ('S08','00731')
insert into #Temp1 Values ('S09','00731')
insert into #Temp1 Values ('S07','00731')
insert into #Temp1 Values ('S04','00741')
insert into #Temp1 Values ('S01','00746')
insert into #Temp1 Values ('S01','00770')
insert into #Temp1 Values ('S01','00771')
insert into #Temp1 Values ('S02','00771')

Val1    Val2
--------------------------
S01     00731
S02     00731
S03     00731
S04     00731
S05     00731
S06     00731
S07     00731
S08     00731
S09     00731
S07     00731
S04     00741
S01     00746
S01     00770
S01     00771
S02     00771

I then use a pivot column to show each unique Val2 value and with a max of 10 Val1 values as columns.

SELECT [Val2],
c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
FROM
(SELECT Val1, Val2
FROM         #TEMP1) AS PivotTable
PIVOT
(
MAX([PivotTable].[Val1])
FOR
Val1
IN
(C1, c2, c3, c4, c5, c6, c7, c8, c9, c10)
) AS PivotTable;

I wish to have results like:

Val2    c1  c2  c3  c4  c5  c6  c7  c8  c9  c10
--------------------------------------------------------------------------------------
00731  S01  S02 S03 S04 S05 S06 S07 S08 S09 S07 
00741  S04  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
00746  S01  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
00770  S01  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
00771  S01  S02 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

But i actually just get all NULL values for the columns:

Val2    c1  c2  c3  c4  c5  c6  c7  c8  c9  c10
--------------------------------------------------------------------------------------
00731  NULL NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
00741  NULL NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
00746  NULL NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
00770  NULL NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
00771  NULL NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

Answer 1

Your requirements are not totally clear but it looks like you are trying to create a new column named c with then a row_number() associated with it -- c1, c2 c3, etc.

If you were to use the following in your subquery:

SELECT Val1, Val2,
  'C'+ cast(row_number() over(partition by Val2 
                              order by val1) as varchar(10)) col
FROM TEMP1

See SQL Fiddle with Demo

You would get the result:

| VAL1 |  VAL2 | COL |
----------------------
|  S01 | 00731 |  C1 |
|  S02 | 00731 |  C2 |
|  S03 | 00731 |  C3 |
|  S04 | 00731 |  C4 |
|  S05 | 00731 |  C5 |
|  S06 | 00731 |  C6 |
|  S07 | 00731 |  C7 |
|  S07 | 00731 |  C8 |
|  S08 | 00731 |  C9 |
|  S09 | 00731 | C10 |
|  S04 | 00741 |  C1 |
|  S01 | 00746 |  C1 |
|  S01 | 00770 |  C1 |
|  S01 | 00771 |  C1 |
|  S02 | 00771 |  C2 |

Which seems to be the result that you then want to PIVOT. You would then apply the PIVOT to this using:

SELECT Val2,
   c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
FROM
(
  SELECT Val1, Val2,
    'C'+ cast(row_number() over(partition by Val2 
                                order by val1) as varchar(10)) col
  FROM TEMP1
) src
PIVOT
(
  MAX(Val1)
  FOR col IN (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10)
) piv;

See SQL Fiddle with Demo. Your final result is then:

|  VAL2 |  C1 |     C2 |     C3 |     C4 |     C5 |     C6 |     C7 |     C8 |     C9 |    C10 |
------------------------------------------------------------------------------------------------
| 00731 | S01 |    S02 |    S03 |    S04 |    S05 |    S06 |    S07 |    S07 |    S08 |    S09 |
| 00741 | S04 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 00746 | S01 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 00770 | S01 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 00771 | S01 |    S02 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

Note: my results are slightly different from what you are requesting as the desired result because I am performing an ORDER BY val1 which causes the S07 values to be grouped together.

There is no order of data in a database unless you request one, so there is no guarantee that one of the S07 values will appear as C10. You could use the following to get the result but there is no guarantee that the result will always be in the correct order:

SELECT Val2,
  c1, c2, c3, c4, c5, c6, c7, c8, c9, c10
FROM
(
  SELECT Val1, Val2,
    'C'+ cast(row_number() over(partition by Val2 
                                order by (select 1)) as varchar(10)) col
  FROM TEMP1
) src
PIVOT
(
  MAX(Val1)
  FOR col IN (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10)
) piv;

See SQL Fiddle with Demo. Using the order by (select 1) alters the order of the data but it does not guarantee that it will always be in that order. The result is:

|  VAL2 |  C1 |     C2 |     C3 |     C4 |     C5 |     C6 |     C7 |     C8 |     C9 |    C10 |
------------------------------------------------------------------------------------------------
| 00731 | S01 |    S02 |    S03 |    S04 |    S05 |    S06 |    S07 |    S08 |    S09 |    S07 |
| 00741 | S04 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 00746 | S01 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 00770 | S01 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |
| 00771 | S01 |    S02 | (null) | (null) | (null) | (null) | (null) | (null) | (null) | (null) |

Answer 2

You can try a standard way of creating a cross-tab or pivot by using correlated subqueries, and use a ranking function (here within a CTE) to determine which column to put your values in:

;with cte as (
    select Val1
        , Val2
        , row_number() over (partition by Val2 order by Val1) as col
        --, row_number() over (partition by Val2 order by Id) as col -- Use this if you have an identity
    from #TEMP1
    --from (select distinct * from #TEMP1) as t -- Use this to rank distinct entries
)
select c.Val2
    , (select Val1 from cte where Val2 = c.Val2 and col = 1) as c1
    , (select Val1 from cte where Val2 = c.Val2 and col = 2) as c2
    , (select Val1 from cte where Val2 = c.Val2 and col = 3) as c3
    , (select Val1 from cte where Val2 = c.Val2 and col = 4) as c4
    , (select Val1 from cte where Val2 = c.Val2 and col = 5) as c5
    , (select Val1 from cte where Val2 = c.Val2 and col = 6) as c6
    , (select Val1 from cte where Val2 = c.Val2 and col = 7) as c7
    , (select Val1 from cte where Val2 = c.Val2 and col = 8) as c8
    , (select Val1 from cte where Val2 = c.Val2 and col = 9) as c9
    , (select Val1 from cte where Val2 = c.Val2 and col = 10) as c10
from cte as c
group by c.Val2
order by c.Val2

Note that there are a few ways to determine which column Val2 belongs in, and I've put up a couple of possibilities in the CTE (some commented). However, your current desired output is impossible to reach since there is currently no way to tell that S07 should be in the tenth column. Perhaps the addition of an identity to the temp table will capture the order of events, if that's what you want. I've included that possibility in the CTE as well.