Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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;
Add Comment
Please, Sign In to add comment