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!
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -