CODEWITHSUNDEEP
sqlsql-serverpivot
GianIsTheName
GianIsTheName

Reputation: 187

Translating a table into a Pivot Table

I'd like to know if it is possible to translate the table below:

enter image description here

into a pivot table like this?

enter image description here

i tried the query below but it is not giving me the right results:

SELECT * FROM
(
    select 
        WorkWeek
        , CometsFT
        , CometsTR
        , CSP
        , MaxCIMFT
        , MaxCIMTR
        , MaxCIMWS
        , STD
    from tblLotTrackingDetails
) s
pivot
(
    sum(cometsft)
    for WorkWeek in (WW6, WW7, WW8, WW9, WW10)
)as piv

Upvotes: 0

Views: 90

Answers (2)

GianIsTheName
GianIsTheName

Reputation: 187

Finally, after a lot of trying, I was able to do what I wanted to do. peterm's suggestion worked. For sharing, below is the dynamic pivot that I will be using:

DECLARE @Columns NVARCHAR(MAX)
        ,@query NVARCHAR(MAX)
SELECT @Columns = STUFF(
 (SELECT  ', ' +'['+WorkWeek+']' FROM
 (SELECT TOP 10 WorkWeek FROM(SELECT TOP 10 WorkWeek , LotTrackingID    FROM tblLotTracking ORDER BY LotTrackingID DESC) s Order by s.LotTrackingID) AS T FOR XML PATH('')),1,2,'') 

SET @query = N'WITH unpivot_details AS
(
  SELECT WorkWeek, Type, Value
    FROM 
  ( 
    SELECT WorkWeek, CometsFT, CometsTR, CSP, MaxCIMFT, MaxCIMTR, MaxCIMWS, STD
      FROM tblLotTrackingDetails 
  ) s
  UNPIVOT
  ( 
    Value FOR Type IN (CometsFT, CometsTR, CSP, MaxCIMFT, MaxCIMTR, MaxCIMWS, STD) 
  ) s
)
SELECT Type RowLabels, 
       ' + @Columns + '
  FROM
(
  SELECT WorkWeek, Type, Value
    FROM unpivot_details
  UNION ALL
  SELECT WorkWeek, ''Total'', SUM(Value) Value
    FROM unpivot_details
   GROUP BY WorkWeek
) s
PIVOT
(
  SUM(Value) FOR WorkWeek IN(' + @Columns + ')
) p
 ORDER BY CASE WHEN Type = ''Total'' THEN 1 ELSE 0 END,
          Type';
EXEC sp_executesql @query;

Upvotes: 0

peterm
peterm

Reputation: 92785

You have to UNPIVOT your table first and then PIVOT it again 

WITH unpivot_details AS
(
  SELECT WorkWeek, Type, Value
    FROM 
  ( 
    SELECT WorkWeek, CometsFT, CometsTR, CSP, MaxCIMFT, MaxCIMTR, MaxCIMWS, STD
      FROM tblLotTrackingDetails 
  ) s
  UNPIVOT
  ( 
    Value FOR Type IN (CometsFT, CometsTR, CSP, MaxCIMFT, MaxCIMTR, MaxCIMWS, STD) 
  ) s
)
SELECT Type RowLabels, 
       WW1, WW2, WW3, WW4, WW5, WW6, WW7, WW8, WW9, WW10
  FROM
(
  SELECT WorkWeek, Type, Value
    FROM unpivot_details
  UNION ALL
  SELECT WorkWeek, 'Total', SUM(Value) Value
    FROM unpivot_details
   GROUP BY WorkWeek
) s
PIVOT
(
  SUM(Value) FOR WorkWeek IN(WW1, WW2, WW3, WW4, WW5, WW6, WW7, WW8, WW9, WW10)
) p
 ORDER BY CASE WHEN Type = 'Total' THEN 1 ELSE 0 END,
          Type

Sample output:

| ROWLABELS | WW1 | WW2 | WW3 | WW4 | WW5 | WW6 | WW7 | WW8 | WW9 | WW10 |
|-----------|-----|-----|-----|-----|-----|-----|-----|-----|-----|------|
|  CometsFT |  42 |  50 |  30 |  45 |  25 |  36 |  26 |  33 |  35 |   35 |
|  CometsTR |  20 |  14 |  30 |  28 |  24 |  14 |  21 |  18 |  21 |    9 |
|       CSP |   6 |   4 |   1 |   7 |   9 |   8 |   8 |   2 |   4 |    3 |
|  MaxCIMFT |  52 |  58 | 105 |  77 |  42 |  39 |  34 |  23 |  34 |   19 |
|  MaxCIMTR |  21 |   2 |   7 |   9 |  34 |   4 |   5 |   7 |   7 |    9 |
|  MaxCIMWS |  28 |  29 |  59 |  82 |  24 |  26 |  19 |  20 |  40 |   21 |
|       STD |   3 |  40 |   2 |   8 |   2 |   5 |   5 |   2 |   9 |    2 |
|     Total | 172 | 197 | 234 | 256 | 160 | 132 | 118 | 105 | 150 |   98 |

Here is SQLFiddle demo

UPDATE based on your comments. This query will pull data for weeks 11 through 15.

WITH unpivot_details AS
(
  SELECT WorkWeek, Type, Value
    FROM 
  ( 
    SELECT WorkWeek, CometsFT, CometsTR, CSP, MaxCIMFT, MaxCIMTR, MaxCIMWS, STD
      FROM tblLotTrackingDetails 
     WHERE WorkWeek IN ('WW11', 'WW12', 'WW13', 'WW14', 'WW15')
  ) s
  UNPIVOT
  ( 
    Value FOR Type IN (CometsFT, CometsTR, CSP, MaxCIMFT, MaxCIMTR, MaxCIMWS, STD) 
  ) s
)
SELECT Type RowLabels, 
       WW11, WW12, WW13, WW14, WW15
  FROM
(
  SELECT WorkWeek, Type, Value
    FROM unpivot_details
  UNION ALL
  SELECT WorkWeek, 'Total', SUM(Value) Value
    FROM unpivot_details
   GROUP BY WorkWeek
) s
PIVOT
(
  SUM(Value) FOR WorkWeek IN(WW11, WW12, WW13, WW14, WW15)
) p
 ORDER BY CASE WHEN Type = 'Total' THEN 1 ELSE 0 END,
          Type

Here is SQLFiddle demo

Upvotes: 1

Related Questions