Hi,

Can anynone help me create a crosstab query in SQL Server?
My table structure is:

projectID batchCode weekNo CompletionAverage
   1 a1 1 10
   1 a1 2 12
   1 a1 3 25
   2 a1 1 20
   2 a1 2 30

I would want to have a crosstab query similar to the one below:

projectID batchCode 1 2 3
   1 a1 10 12 25
   2 a1 20 30

I would like the records to be arranged by projectID with weekNo as the columns. The number of records for each project is indefinite which means the number of columns would be indefinite too. I can easily create this in Access, but I guess there's no TRANSFORM statement in SQL Server 2000. I hope you can help me solve this problem. Thanks very much!

Google for "Dynamic crosstab queries", that should help you.

_________________________
- Vijay G
Strive for Perfection

I'd say, especially since the number of columns is "indefinite", that you should create a spreasheet that uses an "External data source" to create a "pivot" table... "Pivot" and "CrossTab" are nearly synonomous.

--Jeff Moden