SQL Statement Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Statement Help

Author

Topic

GenerationWithoutName
Starting Member

26 Posts

Posted - 2002-10-03 : 03:15:34

I have SQL Statement like this :
SELECT c.CategoryName, p.ProductName, SUM(d.Quantity) AS QTY
FROM Categories AS c INNER JOIN Products AS p ON c.CategoryID=p.CategoryID
INNER JOIN [Order Details] AS d ON p.ProductID=d.ProductID
GROUP BY c.CategoryName, p.ProductName
ORDER BY c.CategoryName, p.ProductName

the result show like this :
CategoryName ProductName QTY
--------------- ---------------------------------------- -----------
Beverages Chai 828
Beverages Chang 1057
Condiments Aniseed Syrup 328
Condiments Genen Shouyu 453
...

i want to display the result this :

CategoryName, Chai, Chang, Aniseed Syrup, Genen Shouyu
Beverages, 828 1057 0 0
Condiments, 0 0 328 453
...

Can anyone help me, please......

DIJE

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-03 : 04:55:53

SUM(case when p.ProductName = 'Chai' then d.Quantity else 0 end) AS Chai ,
SUM(case when p.ProductName = 'Chang' then d.Quantity else 0 end) AS Chang ,
...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

GenerationWithoutName
Starting Member

26 Posts

Posted - 2002-10-08 : 06:32:28

What happen if there is 100...1000, ... rows in Categories Table, and i don't know value of Categories.CategoryName.
I want case statement base on value of Categories.CategoryName...
you know like transform .. in MsAccess.

--->
TRANSFORM Avg(Products.UnitsInStock) AS AvgOfUnitsInStock
SELECT Products.ProductName, Avg(Products.UnitsInStock) AS [Total Of UnitsInStock]
FROM Products
GROUP BY Products.ProductName
PIVOT Products.CategoryID;
<---

DIJE

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-08 : 08:49:17

One way to do this...

SELECT C.Catagoryname,
SUM(CASE WHEN P.ProductName='Chai' THEN 1 ELSE 0 END) as Chai,
SUM(CASE WHEN P.ProductName='Chang' THEN 1 ELSE 0 END) as Chang,
SUM(CASE WHEN P.ProductName='Aniseed' THEN 1 ELSE 0 END) as Aniseed

FROM Categories AS c INNER JOIN Products AS p ON c.CategoryID=p.CategoryID
INNER JOIN [Order Details] AS d ON p.ProductID=d.ProductID
GROUP BY c.CategoryName

Next, someone's going to show us how to do this without CASE.

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-08 : 08:55:52

Sounds like you may need to read Rob's article on Dynamic Cross-Tabs/Pivot Tables.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources