sql-server - SQL Server Pivot 使用多组列

Question

从这样的表中:

CREATE TABLE dbo.mytable 
(
     [ID] int, 
     [Category] INT, 
     [Lh] varchar(30), 
     [Sev] INT, 
     [Risk] INT
)

insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (5, 2, 'Impossible', 4, 10)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 2, 'Unlikely', 3, 13)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 3, 'Possible', 3, 18)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 5, 'Likely', 3, 23)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (6, 6, 'Possible', 3, 18)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (7, 2, 'Impossible', 5, 15)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (8, 2, 'Very Unlikely', 5, 20)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (9, 2, 'Unlikely', 6, 30)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (10, 2, 'Impossible', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (10, 6, 'Impossible', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 1, 'Impossible', 4, 10)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 2, 'Very Unlikely', 5, 20)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (12, 4, 'Impossible', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (13, 2, 'Impossible', 6, 21)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (14, 2, 'Impossible', 6, 21)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (15, 1, 'Very Unlikely', 3, 6)
insert into mytable ([ID], [Category], [Lh], [Sev], [Risk]) values (15, 2, 'Impossible', 5, 15)

我正在尝试创建一个看起来像这样的结果集(请记住，可能有任意数量的类别，但每个类别始终有 3 个组件):

ID  Cat_1_Lh    Cat_1_Sev   Cat_1_Risk  Cat_2_Lh    Cat_2_Sev   Cat_2_Risk  Cat_3_Lh    Cat_3_Sev   Cat_3_Risk  Cat_4_Lh    Cat_4_Sev   Cat_4_Risk  Cat_5_Lh    Cat_5_Sev   Cat_5_Risk  Cat_6_Lh    Cat_6_Sev   Cat_6_Risk
5                                       Impossible      4           10                                              
6                                       Unlikely        3           13      Possible        3           18                                          Likely          3           23      Possible        3           18
7                                       Impossible      5           15                                              
8                                       Very Unlikely   5           20                                              
9                                       Unlikely        6           30                                              
10                                      Impossible      3           6                                                                                                                   Impossible      3            6
12  Impossible      4           10      Very Unlikely   5           20                                          Impossible      3           6                       
13                                      Impossible      6           21                                              
14                                      Impossible      6           21                                              
15  Very Unlikely   3           6       Impossible      5           15                                              

我已经查看并尝试修改这里介绍的一系列枢轴和反枢轴解决方案，包括动态和静态列定义，但它们看起来都没有最不可能工作的机会，所以我不知道该建议哪些可以利用到解决方案中。

对于哪种流程/机制最适合这一点，我将不胜感激。

提前致谢。

Answer 1

如果你不介意动态

Declare @SQL varchar(max) = ''
Declare @BaseCols varchar(max) ='
,Cat_#_Lh   = max(case when Category=# then LH   else '''' end)
,Cat_#_Sev  = max(case when Category=# then cast(Sev  as varchar(25)) else '''' end)
,Cat_#_Risk = max(case when Category=# then cast(Risk as varchar(25)) else '''' end)
'
Select @SQL = @SQL + Replace(@BaseCols,'#',Category) from (Select Distinct Category from myTable) A 
Set @SQL = 'Select ID'+@SQL + ' From myTable Group By ID Order by ID'
Exec(@SQL)

返回