Clever Geek Handbook

Select column values ​​as columns using PIVOT

I have a scenario where I want to display the column values ​​(Val1) for each unique column value (Val2) as a separate column with the maximum number of 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

Then I use a pivot table column to show each unique Val2 and with a maximum of 10 Val1 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 want 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 actually I just get all the 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
+3


source to share


2 answers


Your requirements are not entirely clear, but it appears that you are trying to create a new column with the name c

, and then the associated row_number()

- c1, c2 c3, etc

.

If you must 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 will 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 |

It looks like this is the result you want PIVOT

. Then you will apply to it PIVOT

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 . Then your end result is:

|  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 asking for as the desired result, because I am performing ORDER BY val1

, which results in grouping of values S07

.

There is no data order in the database unless you query for it, so there is no guarantee that one of the values S07

will display as C10

. You can use the following to get the result, but with no guarantee that the result is always 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 . Usage order by (select 1)

changes the order of the data, but does not guarantee that it will always be in the specified order. Result:

|  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) |
+2


source


You can try the standard way to create a cross-tab or pivot table with correlated subqueries and use the ranking function (here, within the 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 several ways to determine which column Val2

belongs to, and I suggested several possibilities in the CTE (some commented). However, your current desired result is not available as there is currently no way to tell what S07

should be in the tenth column. Perhaps adding identity to the temp table will capture the order of events if that's what you want. I have also included this feature in the CTE.

+1


source






More articles:


All Articles