crosstab query help - 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

SQL Server Development (2000)

crosstab query help

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-11-04 : 15:21:17

Hi,

I have to display the query result in report format.

cust prod month amount
ABCD 1234 Nov 08 100
ABCD 1234 DEC 08 100
ABCD 1234 Jan 09 200
XYZ 456 DEC 08 100
XYZ 456 JAN 09 100
XYZ 456 FEB 09 100

The list of month will be rolling 6 months from the current and it going to change dynamically. Please help in reproducing this in crosstab format

cust prod NOV08 DEC08 JAN09 FEB09 MAR09 APR09
ABCD 1234 100 100 200
XYZ 456 0 100 100 100

Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-04 : 15:33:27

look for pivot/cross tabs in articles on this site. it's well covered.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-04 : 15:37:38

[code]
select cust, prod,
max(case when month = 'NOV 08' then amount end) as NOV08,
max(case when month = 'DEC 08' then amount end) as DEC08,
max(case when month = 'JAN 09' then amount end) as JAN09,
max(case when month = 'FEB 09' then amount end) as FEB09,
max(case when month = 'MAR 09' then amount end) as MAR09,
max(case when month = 'APR 09' then amount end) as APR09
from tab
group by cust, prod
order by cust, prod[/code]

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2008-11-04 : 16:50:57

If you really need it in report format then I'd suggest using SQL Reporting Services and creating a Matrix style report. It does the crosstab functionality for you.

---------------------------
EmeraldCityDomains.com

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-11-04 : 18:23:44

thanks all the replies.

As the months are going to dynamic how can change the query to create dynamic column names

thanksfor help
Posting Yak Master

106 Posts

Posted - 2008-11-04 : 19:53:54

Example in the following link worked for dynamic query

http://www.daymap.net/blog/?p=6

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources