Pivot and Unpivot in SQL Server

Question

My Table looks like the following:

There are 50 such parameters and more than 2000 VISIT_IDs.

I have to rotate the table in such a way that VISIT_IDs are columns and the Parameters are rows. I need that so that I can check for each VISIT_ID, what and how many parameters had value as 1.

I know that for pivot or unpivot can be done only by using an aggregate operation such as sum or count. But as Both VISIT_ID and Parameter are unique, how to go to about rotating the table.

Expected Output:

Answer 1

Here you can find a static example and example using dynamic T-SQL statements:

--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;