T-SQL Pivot having multiple column base pivot

Question

Is there any way I can pivot 2 based columns? I have done dynamic pivot using 1 column as bases for pivot but this time I cannot figured it out.

Here is my table schema:

CREATE TABLE [dbo].[ProdOrders]
(
    [ItemCode] [nvarchar](50) NULL,
    [ReleasedDate] [date] NULL,
    [PlanQty] [float] NULL,
    [ActualQty] [float] NULL
)

And I fill the table:

INSERT INTO [dbo].[ProdOrders]([ItemCode], [ReleasedDate], [PlanQty], [ActualQty])
VALUES
  ('0D203-22882-079','2017-08-18',12654,4218),
  ('0D203-22882-079','2017-08-23',15758,5253),
  ('0D203-22882-079','2017-08-27',26263,8754),
  ('0D203-22882-079','2017-09-02',7354,2451),
  ('0D301-05422-079','2017-08-18',31144,10381),
  ('0D301-05422-079','2017-08-18',20612,6871),
  ('0D301-05422-079','2017-08-23',25765,8588),
  ('0D301-05422-079','2017-08-29',19582,6527),
  ('0D301-05422-079','2017-09-04',15459,5153),
  ('0D203-22882-079','2017-09-22',5232,1744),
  ('0D203-22882-079','2017-09-28',13236,4412),
  ('0D203-22882-079','2017-10-03',7693,2564),
  ('0D301-05422-079','2017-09-23',24735,8245),
  ('0D301-05422-079','2017-09-27',19561,6520),
  ('0D301-05422-079','2017-09-06',23755,7918),
  ('0D301-05422-079','2017-09-14',23755,7918),
  ('0D301-05422-079','2017-09-17',29694,9898),
  ('0D203-22882-079','2017-11-01',2263,754),
  ('0D203-22882-079','2017-10-21',15693,5231),
  ('0D203-22882-079','2017-10-20',15968,5323),
  ('0D203-22882-079','2017-10-25',10521,3507),
  ('0D301-05422-079','2017-10-21',23755,7918),
  ('0D301-05422-079','2017-10-29',17816,5939),
  ('0D301-05422-079','2017-11-01',15612,5204),
  ('0D301-05422-079','2017-10-03',20816,6939),
  ('0D301-05422-079','2017-10-11',15612,5204),
  ('0D301-05422-079','2017-10-18',26020,8673)

This is the expected output:

    ItemCode    201708-Plan 201708-Actual   201709-Plan 201709-Actual   201710-Plan 201710-Actual   201711-Plan 201711-Actual
    --------------- ----------- -------------   ----------- -------------   ----------- -------------   ----------- -------------
    0D203-22882-079  54,674      18,225      25,822     8,607       49,875      16,625      2,263       754 
    0D301-05422-079  97,103      32,367      136,959    45,652      104,020     34,673      15,612      5,204

I have to pivot the PlanQty and ActualQty based from the released date using Year and Month concatenated with Actual and Quantity.

I created a pivot script, but I get an error:

DECLARE   @PivotColumns AS NVARCHAR(MAX)
SELECT   @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME([YRMOPLAN]) + QUOTENAME([YRMOACT])
FROM (SELECT DISTINCT  CONVERT(char(6), cast([ReleasedDate] as date), 112) + 'Plan'  as [YRMOPLAN]
,CONVERT(char(6), cast([ReleasedDate] as date), 112) + 'Actual'  as [YRMOACT] FROM [dbo].[ProdOrders]) as PivotQuery ORDER BY [YRMOPLAN] 
SELECT   @PivotColumns 

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
             SELECT ItemCode, CONVERT(char(6), cast([ReleasedDate] as date), 112) + ''Plan''  as [YRMOPLAN]
            ,CONVERT(char(6), cast([ReleasedDate] as date), 112) + ''Actual''  as [YRMOACT], PlanQty,ActualQty  
             FROM ProdOrders
             ) x
             PIVOT 
             (
                 SUM(PlanQty)
                 FOR [YRMOPLAN] IN (' + @PivotColumns + ')
            ) p

            PIVOT 
             (
                 SUM(ActualQty)
                 FOR [YRMOACT] IN (' + @PivotColumns + ')
            ) p2;' 
EXEC SP_EXECUTESQL @query

This is the error I get:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '201708Actual'

Possible duplicate of [SQL Server : dynamic pivot over 5 columns](https://stackoverflow.com/questions/18657214/sql-server-dynamic-pivot-over-5-columns) — Tab Alleman, Aug 24 '17 at 12:36

John Cappelletti · Accepted Answer · 2017-08-25T01:33:44.110

You're missing the comma bewteen [Plan] and [Actual] while constructing @PivotColums

DECLARE   @PivotColumns AS NVARCHAR(MAX)
SELECT   @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME([YRMOPLAN])+',' + QUOTENAME([YRMOACT])
FROM (SELECT DISTINCT  CONVERT(char(6), cast([ReleasedDate] as date), 112) + 'Plan'  as [YRMOPLAN]
,CONVERT(char(6), cast([ReleasedDate] as date), 112) + 'Actual'  as [YRMOACT] FROM [dbo].[ProdOrders]) as PivotQuery ORDER BY [YRMOPLAN] 
SELECT   @PivotColumns

However, you can simplify this a bit

Example

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ItemCode
              ,B.*
         From  [dbo].[ProdOrders] A
         Cross Apply ( values ( convert(varchar(6),ReleasedDate,112)+''-Plan'',PlanQty)
                             ,( convert(varchar(6),ReleasedDate,112)+''-Actual'',ActualQty)
                     ) B (Item,Value)
      ) S
 Pivot (sum([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Plan') 
                                                              +','+QuoteName(convert(varchar(6),ReleasedDate,112)+'-Actual') 
                                               From [dbo].[ProdOrders]
                                               Order By 1 
                                               For XML Path('')),1,1,'')  + ') ) p'
Exec(@SQL);
--Print @SQL

Returns

Thank you. But I have tried your simplified query but an error occur: Msg 195, Level 15, State 10, Line 11 'concat' is not a recognized built-in function name. Msg 137, Level 15, State 2, Line 16 Must declare the scalar variable "@SQL". Msg 137, Level 15, State 2, Line 17 Must declare the scalar variable "@SQL". — Eliseo Jr, Aug 25 '17 at 01:23
@EliseoJr Then your compatibility level is set too low. Concat() is in 2012 — John Cappelletti, Aug 25 '17 at 10:22

T-SQL Pivot having multiple column base pivot

1 Answers1