CODEWITHSUNDEEP
sql-servert-sqlpivot
user3123520
user3123520

Reputation: 41

SQL Server Pivot Table on two columns

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 tale 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.

Upvotes: 2

Views: 72

Answers (3)

gotqn
gotqn

Reputation: 43636

You can use dynamic T-SQL in order to make a pivot for various columns. Here is full working example:

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;

Upvotes: 1

Rally To Me
Rally To Me

Reputation: 39

Alternatively, you can use a Group By clause to pivot.

You'll need to adjust your functions as necessary, I'm using MAX right now since the only data you gave was 1's and possible text, assuming a 1-1 ratio. But you can use an aggregate function as well to string stuff together if there's more than one entry per column per group/id. 

select

id
,GroupName
,max(
CASE 
   WHEN RuleName like 'Exclude1' THEN 1
   ELSE NULL
END) as 'Exclude2'
,max(
CASE 
   WHEN RuleName like 'Exclude2' THEN 1
   ELSE NULL
END) as 'Exclude2'
,max(
CASE 
   WHEN RuleName like 'Exclude3' THEN 1
   ELSE NULL
END) as 'Exclude3'
,max(
CASE 
   WHEN RuleName like 'Exclude4' THEN 1
   ELSE NULL
END) as 'Exclude4'
,max(
CASE 
   WHEN RuleName like 'Include1' THEN 1
   ELSE NULL
END) as 'Include1'
,max(
CASE 
   WHEN RuleName like 'Include2' THEN 1
   ELSE NULL
END) as 'Include2'
,max(
CASE 
   WHEN RuleName like 'Exclude1RuleText' THEN RuleName 
   ELSE NULL
END) as 'Exclude1RuleText'
,max(
CASE 
   WHEN RuleName like 'Exclude2RuleText' THEN RuleName 
   ELSE NULL
END) as 'Exclude2RuleText'
,max(
CASE 
   WHEN RuleName like 'Exclude3RuleText' THEN RuleName 
   ELSE NULL
END) as 'Exclude3RuleText'
,max(
CASE 
   WHEN RuleName like 'Exclude4RuleText' THEN RuleName 
   ELSE NULL
END) as 'Exclude4RuleText'
,max(
CASE 
   WHEN RuleName like 'Include1RuleText' THEN RuleName 
   ELSE NULL
END) as 'Include1RuleText'
,max(
CASE 
   WHEN RuleName like 'Include2RuleText' THEN RuleName 
   ELSE NULL
END) as 'Include2RuleText'


from tablename

group by
id
,GroupName

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

A pivot on a union can generate such duo pivoting.

select *
from 
(
  select Id, GroupName
  , RuleName + 'RuleText' as Col
  , RuleText as Value
  from YourSourceTable

  union all

  select Id, GroupName
  , RuleName
  , format(RuleValue, 'd')
  from YourSourceTable
) Src
pivot
(
  max(Value)
  for Col in (
    [Exclude1], [Exclude2], [Exclude3], [Exclude4], 
    [Include1], [Include2], 
    [Exclude1RuleText], [Exclude2RuleText], 
    [Exclude3RuleText], [Exclude4RuleText], 
    [Include1RuleText], [Include2RuleText]
   )
) Pvt
order by Id, GroupName

Upvotes: 0

Related Questions