Crosstab queries in MS SQL

Am I right in coming to the conclusion that there is no built in method for
creating crosstab queries in MS SQL 2000? I have form the reference under
"Pivot" in SQL books that demonstrates how to build your own but it doesn't
account for scenarios where I don't know how many column headings I require.
I have mocked up this SQL text in MS Access which shows what I want but I
can't seem to generate such a result set in SQL.
Any Ideas?
Mr. Smith
SQL Text:............
TRANSFORM Sum(testoutput.JobCount) AS SumOfJobCount
SELECT testoutput.Client, testoutput.ContractName,
testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year] AS ReqMt
h
FROM testoutput
GROUP BY testoutput.Client, testoutput.ContractName,
testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year]
PIVOT testoutput.TradeCategory;That's correct. To pivot in SQL 2000 you need to know how many columns
there will be because you essentially have to have a case statement for
each column. SQL 2005 is in that respect because they've
introduced the PIVOT & UNPIVOT keywords (part of the SELECT clause
syntax) to make the syntax native to the T-SQL language used in Yukon.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mr. Smith wrote:

> Am I right in coming to the conclusion that there is no built in method fo
r
>creating crosstab queries in MS SQL 2000? I have form the reference under
>"Pivot" in SQL books that demonstrates how to build your own but it doesn't
>account for scenarios where I don't know how many column headings I require
.
>I have mocked up this SQL text in MS Access which shows what I want but I
>can't seem to generate such a result set in SQL.
>Any Ideas?
>Mr. Smith
>SQL Text:............
>TRANSFORM Sum(testoutput.JobCount) AS SumOfJobCount
>SELECT testoutput.Client, testoutput.ContractName,
>testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year] AS ReqM
th
>FROM testoutput
>GROUP BY testoutput.Client, testoutput.ContractName,
>testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year]
>PIVOT testoutput.TradeCategory;
>
>|||Mr. Smith meet RAC:)
www.rac4sql.net