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]

Re: How To write An Sql Crosstable Select statment

Joe Saliba (josephs73@hotm ail.com) writes:[color=blue]
> 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[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp