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

General SQL Server Forums

New to SQL Server Programming

crosstab query

Author

Topic

shohan_db
Starting Member

36 Posts

Posted - 2006-09-02 : 04:33:54

Crosstab concepts

I m using Sql server 2000 version
I want to create a simple crosstab… as like the follows

My table is as like

Date warehouse (WH) Delivery No
7/7/2006 10 10-1
7/7/2006 20 20-1
7/7/2006 20 20-2
7/7/2006 30 30-1
8/7/2006 10 10-2
8/7/2006 10 10-3
11/7/2006 30 30-2
11/7/2006 20 20-3

I want to make a report using crosstab in iReport…..

I want report will be as like

Date WH10 WH20 WH30 total delivery
7/7/2006 1 2 1 4
8/7/2006 2 0 0 2
11/7/2006 0 1 1 2

my questions are:

i create a crosstab procedure and it is running but

01. I want to know the query.
02. How I count/sum the wh in the crosstab? (cause the WH is arrange as row in my database table )

I want step by step instructions
I have not clear concepts about crosstab, but I know crosstab will give the solution of this problem

Please help……

Thanks

shohan

magnetica
Starting Member

36 Posts

Posted - 2006-09-02 : 05:47:13

I havnt look at all five threads but they look exactly the same. Why?

If you think you know it all?! You know nothing!

Rishi Maini SQL2K5 Admin
Yak Posting Veteran

80 Posts

Posted - 2006-09-02 : 06:13:55

CREATE TABLE [dbo].[Table1] (
[DOD] [datetime] NOT NULL ,
[WH] [int] NULL
) ON [PRIMARY]
GO

select * from table1

DOD WH
------------------------------------------------------ -----------
2006-07-07 00:00:00.000 10
2006-07-07 00:00:00.000 20
2006-07-07 00:00:00.000 20
2006-07-07 00:00:00.000 30
2006-08-07 00:00:00.000 10
2006-08-07 00:00:00.000 10
2006-11-07 00:00:00.000 30
2006-11-07 00:00:00.000 20

select DOD,
sum( case wh when 10 then 1 else 0 end ) as '10',
sum( case wh when 20 then 1 else 0 end ) as '20',
sum( case wh when 30 then 1 else 0 end ) as '30',

( sum( case wh when 10 then 1 else 0 end ) + sum( case wh when 20 then 1 else 0 end ) + sum( case wh when 30 then 1 else 0 end )) as 'Total'

from table1
Group by DOD

DOD 10 20 30 Total
------------------------------------------------------ ----------- ----------- ----------- -----------
2006-07-07 00:00:00.000 1 2 1 4
2006-08-07 00:00:00.000 2 0 0 2
2006-11-07 00:00:00.000 0 1 1 2

(3 row(s) affected)

Thanks
Rishi Maini

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-02 : 10:03:36

Also read this

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources