Pivot With Grouping?

Question

I am building a query for a SSRS Report and I have a pivot for locations and quantity. I am getting two rows one for each location but when I pivot I want the end result to be one row.

SELECT * 
FROM ( SELECT DISTINCT  ItemNmbr ,
          ITEMDESC ,
          Location ,
          QtyOnhand ,
          QtyOnOrder ,
          QtyBackordered ,
          TotalInventory ,
          EXTDCOST ,
          InventoryValue 
          FROM #ssrsFinal
) AS p PIVOT
(SUM(QTYOnHand) FOR Location IN ( Loc1, Loc2, HUB, INLAND, Loc, [OLD Loc],[IN TRANSIT] )
) AS pvt;

My result is two rows, How do i get it to just one?

I have tried inserting the pivot results into a temp table and then grouping but it does not carry over the locations in the select.

Results before the Pivot. 3 Different Locations.

Perform an aggregate function, such as SUM(), over the column InventoryValue. — Pieter Geerkens
– Pieter Geerkens, Commented Feb 23, 2016 at 23:57

Fuzzy · Accepted Answer · 2016-02-24 21:11:51Z

I think this is what you are looking for:

You have already made the pivot, you just need to aggregate the result of the pivot table accordingly.

Assumption: Grouping by ItemNmbr,ITEMDESC,EXTDCOST,Loc1,Loc2,HUB,INLAND,Loc,[OLD Loc],[IN TRANSIT]

SELECT    ItemNmbr ,
          ITEMDESC ,
          QtyOnOrder=SUM(QtyOnOrder) ,
          QtyBackordered=SUM(QtyBackordered) ,
          TotalInventory=SUM(TotalInventory) ,
          EXTDCOST,
          InventoryValue = SUM(InventoryValue) ,
          Loc1, 
          Loc2, 
          HUB, 
          INLAND, 
          Loc, 
          [OLD Loc],
          [IN TRANSIT]
FROM ( SELECT DISTINCT  ItemNmbr ,
          ITEMDESC ,
          Location ,
          QtyOnhand ,
          QtyOnOrder ,
          QtyBackordered ,
          TotalInventory ,
          EXTDCOST ,
          InventoryValue 
          FROM #ssrsFinal
) AS p PIVOT
(SUM(QTYOnHand) FOR Location IN ( Loc1, Loc2, HUB, INLAND, Loc, [OLD Loc],[IN TRANSIT] )
) AS pvt
GROUP BY  ItemNmbr ,
          ITEMDESC ,
          EXTDCOST,
          Loc1, 
          Loc2, 
          HUB, 
          INLAND, 
          Loc, 
          [OLD Loc],
          [IN TRANSIT] ;

In Response to the comment:

Sample data set:

CREATE TABLE #ssrsFinal (ItemNmbr VARCHAR(10),
          ITEMDESC VARCHAR(100),
          Location VARCHAR(10),
          QtyOnhand INT,
          QtyOnOrder INT,
          QtyBackordered INT,
          TotalInventory INT,
          EXTDCOST DECIMAL(20,5),
          InventoryValue  DECIMAL(20,2))

INSERT INTO #ssrsFinal
VALUES 
('94185BJGD','SS MESH ROAST PAN, 2X GRILL CLAWS, STAM HNDL, GD','Something',0,0,0,0,14.18000,0),
('94185BJGD','SS MESH ROAST PAN, 2X GRILL CLAWS, STAM HNDL, GD','Something',0,0,0,0,14.18000,0),
('94185BJGD','SS MESH ROAST PAN, 2X GRILL CLAWS, STAM HNDL, GD','Something',3,0,0,3,14.18000,42.54)

In response to the comment "...insert values instead of "something" for location try adding a different location from the examples and then run your query again, it will pull back two rows. I tried INLAND, HUB, and LOC.":

SELECT    ItemNmbr ,
          ITEMDESC ,
          QtyOnOrder=SUM(QtyOnOrder) ,
          QtyBackordered=SUM(QtyBackordered) ,
          TotalInventory=SUM(TotalInventory) ,
          EXTDCOST,
          InventoryValue = SUM(InventoryValue) ,
          Loc1=SUM(Loc1), 
          Loc2=SUM(Loc2), 
          HUB=SUM(HUB), 
          INLAND=SUM(INLAND),
          Loc=SUM(Loc), 
          [OLD Loc]=SUM([OLD Loc]),
          [IN TRANSIT]=SUM([IN TRANSIT])
FROM ( SELECT DISTINCT  ItemNmbr ,
          ITEMDESC ,
          Location ,
          QtyOnhand ,
          QtyOnOrder ,
          QtyBackordered ,
          TotalInventory ,
          EXTDCOST ,
          InventoryValue 
          FROM #ssrsFinal
) AS p PIVOT
(SUM(QTYOnHand) FOR Location IN ( Loc1, Loc2, HUB, INLAND, Loc, [OLD Loc],[IN TRANSIT] )
) AS pvt
GROUP BY  ItemNmbr ,
          ITEMDESC ,
          EXTDCOST  ;

Results:

Unfortunately, that did not do it. It pulls back two rows as before.
@newGuy could you please give me the dataset that is produced by: SELECT DISTINCT ItemNmbr , ITEMDESC , Location , QtyOnhand , QtyOnOrder , QtyBackordered , TotalInventory , EXTDCOST , InventoryValue FROM #ssrsFinal
@newGuy there must be something in your data is different within the group columns that produces two rows. See my edited answer for more details.
@newGuy ah that makes sense so you are not grouping by the pivoted columns. See my edited answer for the result you are looking for

Collectives™ on Stack Overflow

Pivot With Grouping?

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related