Aggregate Transpose Problem

My example code (CODE 1) returns quarter counts for several fields in
Northwind (I'm using Northwind to simulate my actual data). It returns code
that looks like FIGURE 1.
Can my sql be modified so the Field names are the GROUPED BY and run down
the 1st column while forcing the Quarters to be the header columns and
replace the ctShipName, ctShipCity and ctShipCountry positions and result
looking like FIGURE 2?
FIGURE 1 (Current Result):
Quarter ctShipName ctShipCity ctShipCountry
1996, Qtr. 3 185 185 185
1996, Qtr. 4 220 220 220
1997, Qtr. 1 241 241 241
1997, Qtr. 2 253 253 253
1997, Qtr. 3 256 256 256
FIGURE 2 (Desired Result):
Fields 1996, Qtr. 3 1996, Qtr. 4 1997, Qtr. 1
1997, Qtr. 2 1997, Qtr. 3
ctShipName 185 220 241
253 256
ctShipCity 185 220 241
253 256
ctShipCountry 185 220 241
253 256
CODE 1:
SELECT CAST(DATEPART(yyyy, Orders.OrderDate) AS VARCHAR) + ', Qtr. ' +
CAST(DATEPART(q,
Orders.OrderDate) AS VARCHAR) AS Quarter
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity, COUNT(Orders.ShipCountry) AS
ctShipCountry
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
GROUP BY CAST(DATEPART(yyyy, Orders.OrderDate) AS VARCHAR) + ', Qtr. ' +
CAST(DATEPART(q,
Orders.OrderDate) AS VARCHAR)
ORDER BY CAST(DATEPART(yyyy, Orders.OrderDate) AS VARCHAR) + ', Qtr. ' +
CAST(DATEPART(q,
Orders.OrderDate) AS VARCHAR)Scott (sbailey@.mileslumber.com) writes:
> My example code (CODE 1) returns quarter counts for several fields in
> Northwind (I'm using Northwind to simulate my actual data). It returns
> code that looks like FIGURE 1.
> Can my sql be modified so the Field names are the GROUPED BY and run down
> the 1st column while forcing the Quarters to be the header columns and
> replace the ctShipName, ctShipCity and ctShipCountry positions and result
> looking like FIGURE 2?
Yes, but to conserve space, I only include the first two quarters:
SELECT col AS " ",
SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
datepart(Q, O.OrderDate) = 3 THEN
CASE col WHEN 'ctShipName' THEN
CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCity' THEN
CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCountry' THEN
CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0 END
END
ELSE 0
END) AS "1996, Qtr. 3",
SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
datepart(Q, O.OrderDate) = 4 THEN
CASE col WHEN 'ctShipName' THEN
CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCity' THEN
CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
WHEN 'ctShipCountry' THEN
CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0 END
END
ELSE 0
END) AS "1996, Qtr. 4"
FROM (SELECT col = 'ctShipName'
UNION ALL
SELECT 'ctShipCity'
UNION ALL
SELECT 'ctShipCountry') AS names
CROSS JOIN (Orders O
JOIN [Order Details] OD ON O.OrderID = OD.OrderID
JOIN Products P ON P.ProductID = OD.ProductID
JOIN Categories C ON C.CategoryID = P.CategoryID)
GROUP BY col
Now, I presume that in your real-world case, you result set is not
entirely static as here. In that case, you will have to play with
dynamic SQL again.
But before you rush ahead, have a look at http://www.rac4sql.net. This
is a third-party tool which reportedly is very good for crosstabs and
similar. I have never used it myself, but I've heard people speak
positively about it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks, that was a tough one, but I see what I have to do.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9728DBD278581Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Yes, but to conserve space, I only include the first two quarters:
> SELECT col AS " ",
> SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
> datepart(Q, O.OrderDate) = 3 THEN
> CASE col WHEN 'ctShipName' THEN
> CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCity' THEN
> CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCountry' THEN
> CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0
> END
> END
> ELSE 0
> END) AS "1996, Qtr. 3",
> SUM(CASE WHEN datepart(YEAR, O.OrderDate) = 1996 AND
> datepart(Q, O.OrderDate) = 4 THEN
> CASE col WHEN 'ctShipName' THEN
> CASE WHEN O.ShipName IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCity' THEN
> CASE WHEN O.ShipCity IS NOT NULL THEN 1 ELSE 0 END
> WHEN 'ctShipCountry' THEN
> CASE WHEN O.ShipCountry IS NOT NULL THEN 1 ELSE 0
> END
> END
> ELSE 0
> END) AS "1996, Qtr. 4"
> FROM (SELECT col = 'ctShipName'
> UNION ALL
> SELECT 'ctShipCity'
> UNION ALL
> SELECT 'ctShipCountry') AS names
> CROSS JOIN (Orders O
> JOIN [Order Details] OD ON O.OrderID = OD.OrderID
> JOIN Products P ON P.ProductID = OD.ProductID
> JOIN Categories C ON C.CategoryID = P.CategoryID)
> GROUP BY col
> Now, I presume that in your real-world case, you result set is not
> entirely static as here. In that case, you will have to play with
> dynamic SQL again.
> But before you rush ahead, have a look at http://www.rac4sql.net. This
> is a third-party tool which reportedly is very good for crosstabs and
> similar. I have never used it myself, but I've heard people speak
> positively about it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx