CODEWITHSUNDEEP
sql-serverpivot
dtc
dtc

Reputation: 185

sql server pivot multiple fields by two columns

I'm given a messy "fact" table in SQL Server 2012 with OrderId and ProductId being the keys. The same OrderId can have multiple ProductId (up to 5 rows). For reporting purposes I need to leave only one row for each combination OrderId+ProductId, using the max value from the 5 fields Ask, Buy, Rec, Pen, Ret.

I've tried using the pivot operator over ProductId, but don't know how to get the max value from the 5 different fields.

Sample data:

DROP TABLE #tmpSO;

CREATE TABLE #tmpSO
(OrderId INT, ProductId INT, Ask INT, Buy INT, Rec INT, Pen INT, Ret INT);

INSERT INTO #tmpSO (OrderId, ProductId, Ask, Buy, Rec, Pen, Ret)
VALUES
(67,1,2,0,0,2,0),
(67,4,30,0,0,30,0),
(67,4,0,30,0,30,0),
(67,6,20,0,0,0,0),
(67,6,0,20,0,0,0),
(67,6,0,0,20,0,0),
(67,9,30,0,0,0,0),
(67,9,0,30,0,0,0),
(67,9,0,0,25,0,0),
(67,9,0,0,0,5,0);

SELECT * FROM #tmpSO;

OrderId ProductId   Ask Buy Rec Pen Ret
67      1           2   0   0   2   0
67      4           30  0   0   30  0
67      4           0   30  0   30  0
67      6           20  0   0   0   0
67      6           0   20  0   0   0
67      6           0   0   20  0   0
67      9           30  0   0   0   0
67      9           0   30  0   0   0
67      9           0   0   25  0   0
67      9           0   0   0   5   0

My desired result would be:

OrderId ProductId   Ask Buy Rec Pen Ret
67      1           2   0   0   2   0
67      4           30  30  0   30  0
67      6           20  20  20  0   0
67      9           30  30  25  5   0

Please advise if this is feasible using Pivot (or other operator), and a help showing how to use it. Many thanks.

Upvotes: 1

Views: 56

Answers (1)

Rigerta
Rigerta

Reputation: 4039

Why can't you use GROUP BY and MAX()?

It would give your expected result: 

select orderid, productid, MAX(ask) ask, MAX(buy) as buy, MAX(rec) as rec, MAX(pen) as pen, MAX(ret) as ret
from #tmpso 
group by orderid, productid
order by orderid, productid

Working copy here.

Upvotes: 2

Related Questions