使用PIVOT选择列值作为列 [英] Select column values as columns using PIVOT
问题描述
我有一种情况，我希望将每个唯一列值(Val2)的列值(Val1)显示为单独的列，最多10列.
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
然后，我使用数据透视列显示每个唯一的Val2值，并且最多显示10个Val1值作为列.
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;
我希望得到如下结果:
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
但是我实际上只是获得了列的所有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
推荐答案
您的要求尚不完全清楚，但看起来您正在尝试创建一个名为c的新列，然后将其与row_number()关联- c1, c2 c3, etc.
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
请参见带有演示的SQL小提琴
您将得到结果:
| 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 |
您似乎想要PIVOT的结果.然后，您可以使用以下方法将PIVOT应用于此:
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;
请参见带有演示的SQL小提琴.最终的结果是:
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) |
注意:我的结果与您要求的结果略有不同，因为我执行的是ORDER BY val1，这会导致S07值分组在一起.
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.
除非您请求一个，否则数据库中没有数据的顺序，因此无法保证S07值之一将显示为C10.您可以使用以下命令获取结果，但不能保证，该结果将始终按照正确的顺序进行:
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;
请参见带演示的SQL小提琴.使用order by (select 1)会更改数据的顺序，但不能保证数据将始终保持该顺序.结果是:
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) |
这篇关于使用PIVOT选择列值作为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
