Erland:
I am going to have a very similar problem.
I was under the, probably mistaken, impression, from reading BOL, that
SELECT PIVOT was going to be the answer.
Your response to Edward indicates I'm wrong.
Please clarify when we can use PIVOT then.

Many thanks.

John Webb
john-webb@sbcglobal.net

John Webb (john-webb@sbcglobal.net) writes:
[quoted text, click to view]

This question seems to be directed to me. However, there is a lack of
context. Since I answer a couple of questions each day, I don't remember
which one that was Edward's.

SQL 2000 does not have any PIVOT function. As long as you pivot on
static columns, you can do it fairly well with extensive use of the
CASE expresssion:

SELECT jansales = SUM(CASE month(salesdate) = 1 THEN salesamt ELSE 0 END,
febsales = SUM(CASE month(salesdate) = 2 THEN salesamt ELSE 0 END,
...
FROM tbl

If you want dynamic pivoting, a popular third-party tool is RAC,
see http://www.rac4sql.net/

In the current beta of the coming version SQL Server there is a PIVOT
keyword, to make pivoting possible. It is still restricted to static
pivoting. (Dynamic pivoting violates the concept that a SELECT statement
generates a table with known columns.)


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at