SQL Severer - Pivot query - turn rows into columns

Question

Need a pivot query to convert columns (Loc) to rows grouped by (Item_No)

Existing Table:`

 ITEM_NO,   LOC,    Quantity,   Price,  WholSalePrice,  Category 
A18000, Loc1,   11, 100,    80, 1

A18001, LOC2,   22, 101,    81, 1

A18002, LOC3,   33, 102,    79, 1

A18003  ,LOC4,  44, 99, 78, 1

A18004, LOC5,   55, 98, 77, 1

B18000, Loc1,   888 ,10,    8,  2

B18001, LOC2,   999,    11, 7,  2

B18002, LOC3,   887,    12, 9,  2

B18003, LOC4,   885,    13, 6,  2

B18004, LOC5,   887,    14, 5,  2

To Pivot the locations to rows and get the corresponding quantity, price , wholesale price and category

ITEM_NO,    Loc1-Quantity,  Loc1-Price, Loc1-WholSalePrice, Loc1-Category,  Loc2-Quantity,  Loc2-Price, Loc2-WholSalePrice, Loc2-Category,  Loc3-Quantity,  Loc3-Price, Loc3-WholSale Price,

    A18000, 11, 100,    80, 1,  22, 101,    81, 1,  33, 102,    79

    B18000, 888,    10, 8,  2,  999 ,11 ,7, 2,  887,    12  ,9

I am trying to apply the following query on the above scenario, but not working..

USE AdventureWorks;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

Thanks for Help !!

In what way is it not working? Do you recieve an error message (if so what) or unexpected results? — David Rushton, Apr 07 '16 at 15:56
Possible duplicate of [SQL Server Pivot Table with multiple column aggregates](http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates) — Tab Alleman, Apr 07 '16 at 16:00

score 0 · Accepted Answer · answered Apr 07 '16 at 20:15

Instead of doing multiple pivots, you might be better off just using MAX(CASE WHEN) aggregation to build your result. MIN and SUM can be used instead of MAX.This can also be done dynamically just like PIVOT.

SELECT  [ITEM_NO]               = LEFT(ITEM_NO, 5) + '0',
        [Loc1-Quantity]         = MAX(CASE WHEN [LOC] = 'Loc1' THEN [Quantity] END),
        [Loc1-Price]            = MAX(CASE WHEN [LOC] = 'Loc1' THEN [Price] END),
        [Loc1-WholSalePrice]    = MAX(CASE WHEN [LOC] = 'Loc1' THEN [WholSalePrice] END),   
        [Loc1-Category]         = MAX(CASE WHEN [LOC] = 'Loc1' THEN [Category] END),
        [Loc2-Quantity]         = MAX(CASE WHEN [LOC] = 'Loc2' THEN [Quantity] END),
        [Loc2-Price]            = MAX(CASE WHEN [LOC] = 'Loc2' THEN [Price] END),
        [Loc2-WholSalePrice]    = MAX(CASE WHEN [LOC] = 'Loc2' THEN [WholSalePrice] END),   
        [Loc2-Category]         = MAX(CASE WHEN [LOC] = 'Loc2' THEN [Category] END)     
        -- etc... 
FROM    myTable
GROUP BY LEFT(ITEM_NO, 5)

Thanks a lot Jamie !!! I will give it a try and let you know. Have a good day. :) — Blisslights, Apr 11 '16 at 15:55

SQL Severer - Pivot query - turn rows into columns

1 Answers1