How to make pivot table in SQL Server in my case?

Question

I wants to display the record by same column. I don't know how describe the question also.

I have a table called SoldQtyTable

ItemNo    Weeks    Years    QtySold    AsOfWeekOnHand
----------------------------------------------------
1           1        2017      5         3
2           1        2017      2         5
3           1        2017      66        70
1           2        2017      4         33

I wants to display like below

ItemNo    Years    [1QtySold]    [1_OnHand]    [2QtySold]    [2_OnHand]
-----------------------------------------------------------------------
1          2017       5               3            4            33
2          2017       2               5
3          2017      66               70

I tried in this way. But It doesn't work

select 
    PVT1.ItemID,
    PVT1.StoreID,
    PVT1.Years,
    isnull([1],0) as [1QtySold], isnull([2],0) as [2QtySold],
    isnull([1_OnHand],0) as [1_OnHand], isnull([2_OnHand],0) as [2_OnHand]
from 
(
    SELECT
        ItemID,
        StoreID,
        Years,
        Weeks,
        AsOfWeekOnHand
    FROM
        SoldQtyTable
) L
PIVOT
(
    SUM(AsOfWeekOnHand) 
    FOR Weeks IN ( [1_OnHand], [2_OnHand])
) AS PVT1
LEFT JOIN
(
    SELECT
        ItemID,
        StoreID,
        Years,
        Weeks,
        QtySold
    FROM
        SoldQtyTable
) L
PIVOT
(
    SUM(QtySold) 
    FOR Weeks IN ( [1soldQty], [2soldQty]
) AS PVT2 on PVT2.ItemID = PVT1.ItemID and PVT1.Years = PVT2.Years
where 
    PVT1.years = 2017

Doesn't work how? Are you getting an error message you don't understand ? Output is different than you expect? I suggest giving clearer information in the question. Column names on SoldQtyTable and on the output you show don't even agree with the column names in the query. — Mic, Aug 20 '17 at 10:25
I suspect what you are trying to do is add a second pivotted column to a working pivot? — Mic, Aug 20 '17 at 10:26
`FOR Weeks IN ( [1_OnHand], [2_OnHand])` doesn't make sense, it needs to read `FOR Weeks IN ( [1], [2])`. The column names come from the values in the Weeks column. Of course you'll get the same column names in the two pivots, so you need to be saying something like `IsNull(PVT1.[1],0) AS [1_OnHand]` instead of `IsNull([1],0) AS [1_OnHand]`. — Mic, Aug 20 '17 at 10:33

score 2 · Accepted Answer · answered Aug 20 '17 at 12:18

I find conditional aggregation so much simpler:

SELECT ItemID, Years,
       SUM(CASE WHEN weeks = 1 THEN QtySold END) as QtySold_1,          
       SUM(CASE WHEN weeks = 1 THEN AsOfWeekOnHand END) as AsOfWeekOnHand_1,
       SUM(CASE WHEN weeks = 2 THEN QtySold END) as QtySold_2,          
       SUM(CASE WHEN weeks = 3 THEN AsOfWeekOnHand END) as AsOfWeekOnHand_2
FROM SoldQtyTable
GROUP BY ItemID, Years
ORDER BY ItemID, Years;

Vojtěch Dohnal · Answer 2 · 2017-08-20T11:18:46.783

If you want to PIVOT multiple columns, you can achieve that by doing UNPIVOT first and then doing PIVOT on just one value column as described in this answer.

SELECT ItemID,
      StoreID,
      Years,
      [1_QtySold],
      [1_AsOfWeekOnHand] AS [1_OnHand],  
      [2_QtySold],
      [2_AsOfWeekOnHand] AS [2_OnHand]
FROM
(           
    SELECT
        ItemID,
        StoreID,
        Years,
        Weeks + '_' + col AS col,
        [value]
    FROM
    (
        SELECT
            ItemID,
            StoreID,
            Years,
            CAST(Weeks as varchar) Weeks,
            AsOfWeekOnHand,
            QtySold
        FROM
            SoldQtyTable
        WHERE Years = 2017 -- your original filter PVT1.years = 2017
     ) src
     UNPIVOT 
     (
        VALUE
        FOR col in (AsOfWeekOnHand, QtySold)
     ) unpiv
) s
PIVOT
(
  SUM([value])
  FOR col IN ([1_AsOfWeekOnHand], [1_QtySold], [2_AsOfWeekOnHand], [2_QtySold])
) unpiv
ORDER BY StoreID

Here is the SQL Fiddle.

How to make pivot table in SQL Server in my case?

2 Answers2