Katt Katt - 2 months ago 11
SQL Question

SQL/TSQL: Using PIVOT on multiple tables

I'm trying to solve two similar problems with PIVOT in SQL. I've got three tables:


  • First one with columns: orderid, empid, orderdate;

  • Second with columns: orderid, productid, unitprice

  • Third one with columns: empid, firstname, lastname and so on (other information about workers)



Now, I need to use PIVOT to manage something like this:

empid firstname lastname 2006 2007 2008
1 aaa bbb 1 55 77
1 aaa bbb 2 58 79
2 xxx ccc 4 59 82
.......................................


Where numbers in 2006, 2007, 2008 columns are orderids assigned for every year.

I've got something like this (query inside of
Pivott
works fine):

WITH Pivott AS
(
SELECT
so.empid,
YEAR(so.orderdate),
so.orderid,
hr.lastname,
hr.firstname
FROM
Sales.Orders so
LEFT OUTER JOIN
HR.Employees hr ON so.empid = hr.empid
)
SELECT
YEAR(orderdate), [2006], [2007], [2008]
FROM
Pivott
PIVOT
(SUM(orderid) FOR YEAR(orderdate) IN [2006], [2007], [2008]) AS PIV;


And the second problem:

I try to use PIVOT to manage something like this too:

empid firstname lastname 2006 2007 2008
1 aaa bbb 453 34 77
2 vvv kkk 345 89 123
3 xxx ccc 453 12 82
.......................................


Where numbers in 2006, 2007, 2008 columns are the sum of all prices from orders performed by the worker (empid) in a given year.

I've got this:

WITH Pivott AS
(
SELECT
so.empid,
YEAR(so.orderdate) AS YEAR,
so.orderid AS ORDERS,
hr.lastname,
hr.firstname,
s.unitprice AS SUMA
FROM
Sales.Orders so
LEFT OUTER JOIN
HR.Employees hr ON so.empid = hr.empid
INNER JOIN
Sales.OrderDetails s ON so.orderid = s.orderid
)
SELECT
YEAR(orderdate), [2006], [2007], [2008]
FROM
Pivott
PIVOT
(SUM(unitprice) FOR YEAR(orderdate) IN ([2006], [2007], [2008])) AS PIV;


What I need to change to achieve my desired output?

Thank you for your time!

Answer

It looks like you're trying to apply the Pivot command against a CTE

try this;

;WITH pvtCTE AS
(SELECT so.empid,
YEAR(so.orderdate) AS SalesYEAR,
--so.orderid AS ORDERS, /** removed from grouping**/
hr.lastname,
hr.firstname,
SUM(ISNULL(s.unitprice,0.00)) as unitprice /** pre grouping adjusted per comment**/
FROM Sales.Orders so
left outer join HR.Employees hr on so.empid = hr.empid
inner join Sales.OrderDetails s on so.orderid = s.orderid
GROUP BY so.empid, YEAR(so,orderDate), hr.lastname, hr.firstname --added for aggregated CTE results
)

SELECT empid, firstname, lastname, [2006], [2007], [2008]
FROM (SELECT * FROM pvtCTE) p
PIVOT (
SUM(unitprice)
FOR SalesYEAR IN ([2006],[2007],[2008])
) as pvt;
Comments