SQL Server Pivot Table on two columns renaming one column
-
I've the following source table:
---------------------------------------------------------------
| Id | GroupName | RuleName | RuleText | RuleValue |
+-------------------------------------------------------------+
| 1 | Group1 | Exclude1 | Excluded (Reason1) | 1 |
+-------------------------------------------------------------+
| 1 | Group1 | Exclude2 | Excluded (Reason2) | 1 |
+-------------------------------------------------------------+
| 1 | Group1 | Exclude3 | Excluded (Reason3) | 1 |
+-------------------------------------------------------------+
| 1 | Group1 | Include1 | Included (Reason1) | 1 |
+-------------------------------------------------------------+
| 1 | Group1 | Include2 | Included (Reason2) | 1 |
+-------------------------------------------------------------+
| 2 | Group1 | Include1 | Included (Reason1) | 1 |
+-------------------------------------------------------------+
| 2 | Group1 | Exclude4 | Excluded (Reason4) | 1 |
---------------------------------------------------------------I need to unpivot/pivot the table to:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | GroupName | Exclude1 | Exclude2 | Exclude3 | Exclude4 | Include1 | Include2 | Exclude1RuleText | Exclude2RuleText | Exclude3RuleText | Exclude4RuleText | Include1RuleText | Include2RuleText |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Group1 | 1 | 1 | 1 | NULL | 1 | 1 | Excluded (Reason1) | Excluded (Reason2) | Excluded (Reason3) | NULL | Included (Reason1) | Included (Reason2) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2 | Group1 | NULL | NULL | NULL | 1 | 1 | NULL | NULL | NULL | NULL | Excluded (Reason4) | Included (Reason1) | NULL |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------I can't quite figure out how to rename the columns. I must be bit dense today.
-
Just a simple crosstab should do it
SELECT Id, GroupName,
MAX(CASE WHEN RuleName = 'Exclude1' THEN RuleValue END) AS Exclude1,
MAX(CASE WHEN RuleName = 'Exclude2' THEN RuleValue END) AS Exclude2,
MAX(CASE WHEN RuleName = 'Exclude3' THEN RuleValue END) AS Exclude3,
MAX(CASE WHEN RuleName = 'Exclude4' THEN RuleValue END) AS Exclude4,
MAX(CASE WHEN RuleName = 'Include1' THEN RuleValue END) AS Include1,
MAX(CASE WHEN RuleName = 'Include2' THEN RuleValue END) AS Include2,
MAX(CASE WHEN RuleName = 'Exclude1' THEN RuleText END) AS Exclude1RuleText,
MAX(CASE WHEN RuleName = 'Exclude2' THEN RuleText END) AS Exclude2RuleText,
MAX(CASE WHEN RuleName = 'Exclude3' THEN RuleText END) AS Exclude3RuleText,
MAX(CASE WHEN RuleName = 'Exclude4' THEN RuleText END) AS Exclude4RuleText,
MAX(CASE WHEN RuleName = 'Include1' THEN RuleText END) AS Include1RuleText,
MAX(CASE WHEN RuleName = 'Include2' THEN RuleText END) AS Include2RuleText
FROM MyTable
GROUP BY Id, GroupName
ORDER BY Id, GroupName;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I forgot to mention, the column list is not fixed, its dynamic. Your solution only works for a fixed column list.
This is what I started with, but haven't found a way to include the RuleText.
-- Drop source table if it exists
IF ( OBJECT_ID('tempdb..#SourceTable' ) IS NOT NULL ) DROP TABLE [#SourceTable] ;
-- Create source table
CREATE TABLE [#SourceTable]
(
[Id] INT NOT NULL ,
[GroupName] VARCHAR(32) NOT NULL ,
[RuleName] VARCHAR(32) NOT NULL ,
[RuleText] VARCHAR(128) NOT NULL ,
[RuleValue] INT NOT NULL
) ;
-- Populate source table
INSERT INTO [#SourceTable]
( [Id] , [GroupName] , [RuleName] ,[RuleText] , [RuleValue] )
VALUES
( 1 , 'Group1' , 'Exclude1' , 'Excluded (Reason1)' , 1 ) ,
( 1 , 'Group1' , 'Exclude2' , 'Excluded (Reason2)' , 1 ) ,
( 1 , 'Group1' , 'Exclude3' , 'Excluded (Reason3)' , 1 ) ,
( 1 , 'Group1' , 'Include1' , 'Included (Reason1)' , 1 ) ,
( 1 , 'Group1' , 'Include2' , 'Included (Reason2)' , 1 ) ,
( 2 , 'Group1' , 'Include1' , 'Included (Reason1)' , 1 ) ,
( 2 , 'Group1' , 'Exclude4' , 'Excluded (Reason4)' , 1 ) ;
-- Variables used.
DECLARE @pivotColumns NVARCHAR(MAX) ;
DECLARE @sqlQuery NVARCHAR(MAX) ;
-- Dynamically retrieve the columns names for the pivot
SELECT @pivotColumns = COALESCE( @pivotColumns + ',' , '' ) + QUOTENAME( [RuleName] )
FROM ( SELECT DISTINCT [RuleName] FROM [#SourceTable] ) AS src ;
-- SQL to query to get pivoted data
SET @sqlQuery = N'SELECT * FROM '
+ N'( '
+ N' SELECT DISTINCT '
+ N' [Id] , '
+ N' [GroupName] , '
+ N' [RuleName] , '
+ N' [RuleValue] '
+ N' FROM [#SourceTable] '
+ N') t '
+ N'PIVOT( MAX( [RuleValue] ) '
+ N'FOR [RuleName] IN ( ' + @pivotColumns + ') ) AS p' ;
-- Execute SQL query to get pivoted data
EXECUTE sp_executesql @sqlQuery ; -
You'll need a dynamic crosstab
-- Variables used.
DECLARE @sqlQuery NVARCHAR(MAX) ;
DECLARE @sqlQuery1 NVARCHAR(MAX) ;
DECLARE @sqlQuery2 NVARCHAR(MAX) ;
SELECT @sqlQuery1 = (
SELECT ',MAX(CASE WHEN RuleName = '''+RuleName+''' THEN RuleValue END) AS '+RuleName+'
' AS "text()"
FROM #SourceTable
GROUP BY RuleName
ORDER BY RuleName
FOR XML PATH(''),TYPE).value('./text()[1]','NVARCHAR(MAX)')
SELECT @sqlQuery2 = (
SELECT ',MAX(CASE WHEN RuleName = '''+RuleName+''' THEN RuleText END) AS '+RuleName+'RuleText
' AS "text()"
FROM #SourceTable
GROUP BY RuleName
ORDER BY RuleName
FOR XML PATH(''),TYPE).value('./text()[1]','NVARCHAR(MAX)')
-- SQL to query to get pivoted data
SET @sqlQuery = N'SELECT Id, GroupName'
+ @sqlQuery1
+ @sqlQuery2
+ N'FROM #SourceTable
GROUP BY Id, GroupName
ORDER BY Id, GroupName;' ;
-- Execute SQL query to get pivoted data
EXECUTE sp_executesql @sqlQuery ;____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
This is what I finally used:
DROP TABLE IF EXISTS #DataSource;
CREATE TABLE #DataSource
(
[ID] INT
,[GroupName] VARCHAR(12)
,[RuleName] VARCHAR(12)
,[RuleText] VARCHAR(32)
,[RuleValue] BIT
);
INSERT INTO #DataSource ([ID], [GroupName], [RuleName], [RuleText], [RuleValue])
VALUES (1, 'Group1', 'Exclude1', 'Excluded (Reason1)', 1)
,(1, 'Group1', 'Exclude2', 'Excluded (Reason2)', 1)
,(1, 'Group1', 'Exclude3', 'Excluded (Reason3)', 1)
,(1, 'Group1', 'Include1', 'Included (Reason1)', 1)
,(1, 'Group1', 'Include2', 'Included (Reason2)', 1)
,(2, 'Group1', 'Include1', 'Included (Reason1)', 1)
,(2, 'Group1', 'Exclude4', 'Excluded (Reason4)', 1);
DECLARE @DynamicTSQLStatement NVARCHAR(MAX);
DECLARE @DynamicSelectColumns NVARCHAR(MAX);
WITH DataSource ([RowID], [RowValue]) AS
(
SELECT DISTINCT 0, [RuleName]
FROM #DataSource
UNION ALL
SELECT DISTINCT 1, [RuleName] + 'Text'
FROM #DataSource
)
SELECT @DynamicSelectColumns = STUFF
(
(
SELECT ',' + QUOTENAME([RowValue])
FROM DataSource
ORDER BY [RowID], [RowValue]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1
,1
,''
);
SET @DynamicTSQLStatement = N'
SELECT *
FROM
(
SELECT [ID]
,[GroupName]
,[RuleName]
,CAST([RuleValue] AS VARCHAR(32))
FROM #DataSource
UNION ALL
SELECT [ID]
,[GroupName]
,[RuleName] + ''Text''
,[RuleText]
FROM #DataSource
) DS ([ID], [GroupName], [column], [value])
PIVOT
(
MAX([value]) FOR [column] IN (' + @DynamicSelectColumns + ')
) PVT
';
EXECUTE sp_executesql @DynamicTSQLStatement;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply