Hi,

would like to know how to write a crosstable select statment in sql
server2000 where having:
- ItemNumber, ItemDescription, ItemColor, ItemSize as rows
- Stores as columns
- Qty * Netttc as value

from Table sales

thx

*** Sent via Developersdex http://www.developersdex.com ***

Hi

Posting DDL and example data and expected output helps to answer your query
more easily see http://www.aspfaq.com/etiquett*e.asp?id=5006

If you had a Items Table

CREATE TABLE Items ( ItemNumber int, Description varchar(50), Color
char(10), Size int )

CREATE TABLE SaleItems ( ItemNumber int, Store varchar(50), Qty int, Nettttc
decimal(8,3))

Then something like:

SELECT I.ItemNumber, I.Description, I.Color , I.Size
SUM(CASE WHEN S.Store = 'London' THEN S.Qty*S.Nettttc END) AS [London],
SUM(CASE WHEN S.Store = 'Paris' THEN S.Qty*S.Nettttc END) AS [Paris],
SUM(CASE WHEN S.Store = 'Madrid' THEN S.Qty*S.Nettttc END) AS [Madrid]
FROM Items I
LEFT JOIN SaleItems S ON I.ItemNumber = S.ItemNumber
GROUP BY I.ItemNumber, I.Description, I.Color , I.Size

may be something similar to what you want.


John

"Joe Saliba" <josephs73 (AT) hotmail (DOT) com> wrote