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

After insert trigger fires even though no insert actually occured

SQL Server 2000 SP3a
Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine fires
on an insert being run but with no actual inserts occuring.
ie I have a sproc which inserts into a table, I've checked the select
statement of the insert and it returns no rows, so I presumed this meant tha
t
the trigger would not fire as no rows are being inserted, but it does.
======================
-- The basic spoc is this, the select returns no rows, so no inserts should
occur':
insert into table_a (
a_col_1,
a_col_2)
select
b_col_1,
b_col_2
from some_table b
left outer join table_a on
b.pkey = a.pkey
where
a.pkey is null
=======================
======================
-- The basic trigger is
Create trigger tg_Trigger_Name
on A_Table
after insert, update
if update (col_a)
begin
update some other table set some stuff
end
======================
I've looked through BOL, but it simply sais after insert/update trigger
fires after completion.
Thanks for any pointers.> if update (col_a)
> begin
> update some other table set some stuff
> end
The trigger will still fire, even if no rows are affected. So, you should
base any activities in the trigger on whether or not anything actually
happened. I typically wrap my DML trigger activities inside of the
following:
IF @.@.ROWCOUNT > 0
BEGIN
.. stuff here
END
For an insert trigger, you could equally say:
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
.. stuff here
END
A|||This is the way it works. This is not bad, if your actions inside the
trigger are based on contents of the inserted table, because this table is
empty. However, you can use the following code to skip the logic inside the
trigger (the same is true for updates):
if not exists(select * from inserted)
return
Leonid.
"Steve'o" <Steveo@.discussions.microsoft.com> wrote in message
news:55564004-68B8-4879-9F24-7BE5CF2E8F43@.microsoft.com...
> SQL Server 2000 SP3a
> Have I totally missunderstood and AFTER INSERT, UPDATE trigger as mine
> fires
> on an insert being run but with no actual inserts occuring.
> ie I have a sproc which inserts into a table, I've checked the select
> statement of the insert and it returns no rows, so I presumed this meant
> that
> the trigger would not fire as no rows are being inserted, but it does.
> ======================
> -- The basic spoc is this, the select returns no rows, so no inserts
> should
> occur':
> insert into table_a (
> a_col_1,
> a_col_2)
> select
> b_col_1,
> b_col_2
> from some_table b
> left outer join table_a on
> b.pkey = a.pkey
> where
> a.pkey is null
> =======================
> ======================
> -- The basic trigger is
> Create trigger tg_Trigger_Name
> on A_Table
> after insert, update
> if update (col_a)
> begin
> update some other table set some stuff
> end
> ======================
> I've looked through BOL, but it simply sais after insert/update trigger
> fires after completion.
> Thanks for any pointers.