SQl 2000 Cross Tab Report - 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

SQl 2000 Cross Tab Report

Author

Topic

ahaile
Starting Member

25 Posts

Posted - 2009-09-01 : 13:27:44

I am working on a report to display range of dates horizontal as column name. The data is currently designed to be displayed vertical. Query uses a date fileter (where date between @startDate AND @endDate). No null value on this query.

Any help or direction greatly appreciated.

Current status

Name In Out

Alex 12/8/2008 08:00 12/8/2008 16:00
Alex 12/9/2008 08:00 12/8/2008 16:00
Alex 12/10/2008 09:00 12/8/2008 17:00
George 12/8/2008 08:00 12/8/2008 16:00
George 12/9/2008 08:00 12/8/2008 16:00

Expecteed Result

Name 12/8/2008 12/9/2008 12/10/2008

Alex 08:00-16:00 08:00-16:00 09:00-17:00
George 08:00-16:00 08:00-16:00 16:00-22:00

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-01 : 14:06:22

Sample Data

declare @t table ([name] varchar(20),in_time datetime, out_time datetime)
insert @t
select 'Alex', '2008-08-12 08:00', '2008-08-12 16:00' union all
select 'Alex', '2008-09-12 08:00', '2008-09-12 16:00' union all
select 'Alex', '2008-10-12 09:00', '2008-10-12 17:00' union all
select 'George', '2008-08-12 08:00', '2008-08-12 16:00' union all
select 'George', '2008-09-12 08:00', '2008-09-12 16:00' union all
select 'George', '2008-10-12 16:00', '2008-10-12 22:00'

Query

SELECT   [name], 
         max(CASE convert(VARCHAR(10),in_time,111) 
               WHEN '2008/08/12' 
               THEN convert(VARCHAR(8),in_time,108) + '-' + convert(VARCHAR(8),out_time,108) 
               ELSE NULL 
             END) AS [12/08/2008], 
         max(CASE convert(VARCHAR(10),in_time,111) 
               WHEN '2008/09/12' 
               THEN convert(VARCHAR(8),in_time,108) + '-' + convert(VARCHAR(8),out_time,108) 
               ELSE NULL 
             END) AS [12/09/2008], 
         max(CASE convert(VARCHAR(10),in_time,111) 
               WHEN '2008/10/12' 
               THEN convert(VARCHAR(8),in_time,108) + '-' + convert(VARCHAR(8),out_time,108) 
               ELSE NULL 
             END) AS [12/10/2008] 
FROM     @t 
GROUP BY [name]

Result

name                 12/08/2008        12/09/2008        12/10/2008
-------------------- ----------------- ----------------- -----------------
Alex                 08:00:00-16:00:00 08:00:00-16:00:00 09:00:00-17:00:00
George               08:00:00-16:00:00 08:00:00-16:00:00 16:00:00-22:00:00

ahaile
Starting Member

25 Posts

Posted - 2009-09-01 : 14:20:25

Vijay - How do i display the date as column (12/08/2008, 12/09/2008 etc) without hardcoding it?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-09-01 : 14:28:52

You need to use dynamic cross-tabs. I'm not sure how it needs to be done in SQL server 2000. You can search sqlteam for "dynamic cross-tabs"...you will definitely find what you need.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 03:53:15

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.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