在SQL Server中透视和取消透视

在这里,您可以找到使用动态T-SQL语句的静态示例和示例:

--DROP TABLE IF EXISTS [dbo].[DataSource]; 

CREATE TABLE [dbo].[DataSource]
(
    [VISIT_ID] INT
   ,[Parameter1] TINYINT
   ,[Parameter2] TINYINT
   ,[Parameter3] TINYINT
);

INSERT INTO [dbo].[DataSource] ([VISIT_ID], [Parameter1], [Parameter2], [Parameter3])
VALUES (100, 1, 2, 1)
      ,(101, 4, 2, 1)
      ,(102, 2, 2, 0);

-- static

SELECT *
FROM [dbo].[DataSource]
UNPIVOT
(
    [value] FOR [column] IN ([Parameter1], [Parameter2], [Parameter3])
) UNPVT
PIVOT
(
    MAX([value]) FOR [VISIT_ID] IN ([100], [101], [102])
) PVT;

--- danymic

DECLARE @DynamicTSQLStatement NVARCHAR(MAX)
       ,@DynamicColumnsP NVARCHAR(MAX)
       ,@DynamicColumnsU NVARCHAR(MAX);

SET @DynamicColumnsP = STUFF
                        (
                            (
                                SELECT ',[' + [name] + ']'
                                FROM [sys].[columns]
                                WHERE [name] LIKE 'Parameter%'
                                    AND [object_id] = OBJECT_ID('[dbo].[DataSource]')
                                ORDER BY [name]
                                FOR XML PATH('') ,TYPE
                            ).value('.', 'NVARCHAR(MAX)')
                            ,1
                            ,1
                            ,''
                        );

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

SET @DynamicTSQLStatement = N'
SELECT *
FROM [dbo].[DataSource]
UNPIVOT
(
    [value] FOR [column] IN (' + @DynamicColumnsP + ')
) UNPVT
PIVOT
(
    MAX([value]) FOR [VISIT_ID] IN (' + @DynamicColumnsU + ')
) PVT;';

EXEC sp_executesql @DynamicTSQLStatement;