gpt4 book ai didi

sql - 使用 PIVOT 选择列值作为列

转载 作者:行者123 更新时间:2023-12-02 00:11:57 27 4
gpt4 key购买 nike

我有一个场景,我希望将每个唯一列值 (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 等

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

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

参见 SQL Fiddle with Demo

你会得到结果:

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

参见 SQL Fiddle with Demo .那么您的最终结果是:

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

参见 SQL Fiddle with Demo .使用 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) |

关于sql - 使用 PIVOT 选择列值作为列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/14708587/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com