使用 PIVOT 选择列值作为列

我有一个场景，我希望将每个唯一列值 (Val2) 的列值 (Val1) 显示为单个列，最多 10 列。

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 值作为列。

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 值：

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.

如果您要在子查询中使用以下内容：

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

See SQL 摆弄演示 http://sqlfiddle.com/#!3/eebf4/13

你会得到结果：

| 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对此使用：

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 摆弄演示 http://sqlfiddle.com/#!3/eebf4/15。那么你的最终结果是：

|  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要组合在一起的值。

除非您请求一个，否则数据库中的数据没有顺序，因此不能保证其中之一S07值将显示为C10。您可以使用以下内容来获得结果，但有没有保证结果将始终按正确的顺序排列：

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 摆弄演示 http://sqlfiddle.com/#!3/eebf4/16。使用order by (select 1)改变数据的顺序，但不保证它总是按该顺序。结果是：

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