Crosstab Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Crosstab Query

Author

Topic

DClayton77
Starting Member

11 Posts

Posted - 2005-03-30 : 06:08:08

Hi,

I need to create a crosstab in a stored procedure. An example of my base data is as follows:

CustomerGroupID GroupDesc ProductID Quantity
1 Group 1 159 200
1 Group 1 171 50
2 Group 2 159 50
2 Group 2 189 250
2 Group 2 222 100
3 Group 3 159 500
3 Group 3 171 500
3 Group 3 189 500
3 Group 3 222 500

I need the result to be as follows:

ProductID Group 1 Group 2 Group 3
159 200 50 500
171 50 0 500
189 0 250 500
222 0 100 500

I know that i could achieve the above using case statements but the problem is that i don't know how many groups there are going to be. It needs to be dynamic!

I used ms access to create a crosstab which worked perfect. However, using the generated sql won't work because sql 2000 doesn't seem to support the TRANSFORM or PIVOT functions.

Any ideas?

--------------------------------------------------------------------

You know, if there's one thing I've learned from being in the army, it's never ignore a pooh-pooh. I knew a major: got pooh-poohed; made the mistake of ignoring the pooh-pooh -- he pooh-poohed it. Fatal error, because it turned out all along that the soldier who pooh-poohed him had been pooh-poohing a lot of other officers, who pooh-poohed their pooh-poohs. In the end, we had to disband the regiment -- morale totally destroyed ... by pooh-pooh!

andy8979
Starting Member

36 Posts

Posted - 2005-03-30 : 06:31:23

I guess the following url can be of a help for you
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46796

DClayton77
Starting Member

11 Posts

Posted - 2005-03-30 : 06:35:24

Hi,

Thanks for the reply. I also found the perfect answer at

http://www.sqlteam.com/item.asp?ItemID=2955

Thanks again.

DClayton77
Starting Member

11 Posts

Posted - 2005-03-30 : 15:14:29

Thought i would just post a fix to a problem i had with the code from the example. I kept getting an error when logged in as anything other than sa.

The error was something like "incorrect syntax near the word 'END'"

It turns out that the crosstab stored procedure is looking in the tempdb database for some schema info. The user running the stored procedure must be a member of the db_datareader role in the tempdb database.

Hope this helps anyone with the same problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources