sql - 基于特定 ID 的数据透视数据，无需硬编码

Question

我有一个名为 ParamDetails 的表，其中包含以下数据

Aslvl_ID    Value   Score      Comments
1        123.0000   12.3000     Usha P-6914
1        321.0000   64.2000     Usha P-6914
1        456.0000   136.8000    Usha P-6914
1        654.0000   261.6000    Usha P-6914

6        147.0000   14.7000     Bhaskar L-17957
6        741.0000   148.2000    Bhaskar L-17957
6        258.0000   77.4000     Bhaskar L-17957
6        852.0000   340.8000    Bhaskar L-17957

11       4569.0000  456.9000    Murali Prasad K-21632
11       9654.0000  1930.8000   Murali Prasad K-21632
11       4789.0000  1436.7000   Murali Prasad K-21632
11       9874.0000  3949.6000   Murali Prasad K-21632

要求是编写一个查询以显示以下格式的数据。我已经尝试过 pivot 和其他一些东西但没有找到任何成功。请帮助我查询或建议以获得预期的输出。

预期输出:

1 Value    1 score     1 Comments    6 Value    6 score     6 Comments        11 Value    11 score     11 Comments 
123.0000    12.3000     Usha P-6914  147.0000   14.7000     Bhaskar L-17957   4569.0000  456.9000    Murali Prasad K-21632
321.0000    64.2000     Usha P-6914  741.0000   148.2000    Bhaskar L-17957   9654.0000  1930.8000   Murali Prasad K-21632
456.0000    136.8000    Usha P-6914  258.0000   77.4000     Bhaskar L-17957   4789.0000  1436.7000   Murali Prasad K-21632
654.0000    261.6000    Usha P-6914  852.0000   340.8000    Bhaskar L-17957   9874.0000  3949.6000   Murali Prasad K-21632

Answer 1

您可以使用动态查询来实现。

DECLARE @ColumnNames NVARCHAR(MAX) =''
SELECT  @ColumnNames = @ColumnNames + ', ' + QUOTENAME ( ColName + ' Value' ) + ', ' + QUOTENAME ( ColName +' Score' )  + ', ' + QUOTENAME ( ColName +' Comments' )  
FROM (SELECT DISTINCT CONVERT(VARCHAR,Aslvl_ID) ColName, Aslvl_ID FROM ParamDetails) AS T ORDER BY Aslvl_ID 
SET @ColumnNames = STUFF(@ColumnNames,1,1,'')



DECLARE @SqlText NVARCHAR(MAX)
SET @SqlText  = 'SELECT * FROM 
                    (SELECT ROW_NUMBER() OVER(PARTITION BY Aslvl_ID ORDER BY Aslvl_ID) RN,  CONVERT(VARCHAR,Aslvl_ID) +'' Value'' Col, CONVERT(VARCHAR,Value) Val FROM ParamDetails 
                       UNION ALL
                    SELECT  ROW_NUMBER() OVER(PARTITION BY Aslvl_ID ORDER BY Aslvl_ID) RN, CONVERT(VARCHAR,Aslvl_ID) +'' Score'' Col, CONVERT(VARCHAR,Score) Val FROM ParamDetails 
                       UNION ALL
                    SELECT  ROW_NUMBER() OVER(PARTITION BY Aslvl_ID ORDER BY Aslvl_ID ) RN, CONVERT(VARCHAR,Aslvl_ID) +'' Comments'' Col, Comments Val FROM ParamDetails 
                    ) SRC
                    PIVOT (MAX(Val) FOR Col IN (' + @ColumnNames + ') ) PVT'

EXEC sp_executesql @SqlText

结果:

RN  1 Value          1 Score          1 Comments       6 Value          6 Score          6 Comments       11 Value         11 Score         11 Comments
--- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- -----------------------
1   123.0000         12.3000          Usha P-6914      147.0000         14.7000          Bhaskar L-17957  4569.0000        456.9000         Murali Prasad K-21632
2   321.0000         64.2000          Usha P-6914      741.0000         148.2000         Bhaskar L-17957  9654.0000        1930.8000        Murali Prasad K-21632
3   456.0000         136.8000         Usha P-6914      258.0000         77.4000          Bhaskar L-17957  4789.0000        1436.7000        Murali Prasad K-21632
4   654.0000         261.6000         Usha P-6914      852.0000         340.8000         Bhaskar L-17957  9874.0000        3949.6000        Murali Prasad K-21632