Create Fake Crosstab

[CrystalVis]

I have a result table with the following data layout (no key field, this is it):
As_of_Date Email_Created Email_Sent No_Subscribers
9/11/2003 121 70 485
9/18/2003 180 87 1308
9/25/2003 249 148 2460
etc...
How can I format so that the data will look like this


9/11/2003 9/18/2003 9/25/2003...
Email_Created 121 180 249
Email_Sent 70 87 148
No_Subscribers 485 1308 2460

I want to create a crosstab layout but reverse the columns and rows as you see in the example. Your help/suggestion is greatly appreciated.
Thanks

 

[mikeyb540]

well... this is very easy in Ms-Access
TRANSFORM....PIVOT

I havent seen anything for T-SQL yet that can do it.
I read stuff about using SQL 2000 with XML.

 

[CrystalVis]

I need sql expert help!!!Can this be accomplish in SQL?

 

[JamesLean]

Crosstab queries can be done in TSQL but they're not straightforward, even less so when you have a variable amount of columns as you have in your example.

Do a search in this forum for "crosstab" - there are lots of posts on this subject as it crops up quite regularly.

--James

 

[mimi2]

i don't know if this will help.
good luck.

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

 

[CrystalVis]

Thank you all for your input/suggestion. I knew this would be tough accomplish. Right now, I'm using Crystal Report to create a fake crosstab. The data is perfectly displayed for the first 14 weeks. After that, it is totally messy. That is why I need to figure this out in SQL. If you can think of anything else, please let me know.

mimi2,
thanks for the link. i'll give it a try.