sql - 将逐行表转换为逐列

Question

我有一个按列格式设计的表格，如下所示：

+------+---------+-------------+------------+
| S_ID | S_NAME  | MARK_NUMBER | MARK_VALUE |
+------+---------+-------------+------------+
|    1 | TEST    | MARK1       |         50 |
|    1 | TEST    | MARK2       |         60 |
|    1 | TEST    | MARK3       |         70 |
|    2 |  TEST_! | MARK1       |         40 |
|    2 |  TEST_! | MARK2       |         50 |
|    2 |  TEST_! | MARK3       |         40 |
+------+---------+-------------+------------+

MARK_NUMBER列将有“N”个标记。我无法定义它/将其配置为静态的（如其他 SO 答案中所述）。我需要将此表转换为按列格式，如下所示：

 S_ID   S_Name  MARK1   MARK2   MARK3
   1    TEST    50        60    70
   2    TEST_!  40        50    40

标签： sqlsql-servertsqlpivotdynamic-pivot

Answer 1

尝试这个：

CREATE TABLE #DataSource
(
    [S_ID] INT
   ,[S_NAME] VARCHAR(12)
   ,[MARK_NUMBER] VARCHAR(12)
   ,[MARK_VALUE] INT
);

INSERT INTO #DataSource ([S_ID], [S_NAME], [MARK_NUMBER], [MARK_VALUE])
VALUES (1, 'TEST', 'MARK1', 50)
      ,(1, 'TEST', 'MARK2', 60)
      ,(1, 'TEST', 'MARK3', 70)
      ,(2, 'TEST_!', 'MARK1', 40)
      ,(2, 'TEST_!', 'MARK2', 50)
      ,(2, 'TEST_!', 'MARK3', 40);

DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);


SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                SELECT ',[' + CAST([MARK_NUMBER] AS VARCHAR(12)) + ']'
                                FROM #DataSource
                                GROUP BY [MARK_NUMBER]
                                ORDER BY [MARK_NUMBER]
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );

SET @DynammicTSQLStatement = N'
SELECT *
FROM #DataSource
PIVOT
(
    MAX([MARK_VALUE]) FOR [MARK_NUMBER] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynammicTSQLStatement;

DROP TABLE #DataSource;