How To write An Sql Crosstable Select statment

[Joe Saliba]

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 ***

[Erland Sommarskog]

Joe Saliba (jo*******@hotmail.com) writes:

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

The problem here is that the result of SELECT statement is a table, and
in a relational DBMS, a table should have a fixed set of a columns.
Assuming that the number of stores than can change over time, the above
request - reasonable as it may be - does really fit in well. To achieve
the result, you will need to endulge in a lot of dynamic SQL. You may find
that it's easier to this client-side.

Nevertheless, a standard recommendation to this in SQL, is to look at
RAC, a third-party tool. I have never used it myself, though. Check out
http://www.rac4sql.net/
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp