Aggregate Transpose Problem

10 Dec 2005 7:12 PM

Scott

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)

10 Dec 2005 8:36 PM

Erland Sommarskog

Scott (sbai***@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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

10 Dec 2005 9:50 PM

Scott

thanks, that was a tough one, but I see what I have to do.

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9728DBD278581Yazorman@127.0.0.1...
> Scott (sbai***@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, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx