Cross Tab 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

SQL Server 2000 Forums

Transact-SQL (2000)

Cross Tab Query

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-04-03 : 13:14:12

I have a table called summary. This table contains calldate, projects, hours.
I need to create a report/table that shows the date down the left column and
the projects across the top.

Date ProjectA ProjectB ProjectC
20060401 12 0 2
20060402 2 5 4
20060403 3 5 1

How can I accomplish this?
Any help would be greatly appreciated.


CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20)  NULL ,
[dHours] numeric (10,4) NULL)  

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)

INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)

Thanks,
Ninel

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-03 : 13:23:39

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

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-03 : 13:25:51

select [Date] = t.sCalldate
ProjectA = (select dhours from #TEST t2 where t2.sCalldate = t.sCalldate and t2.sProject = 'A') ,
ProjectB = (select dhours from #TEST t2 where t2.sCalldate = t.sCalldate and t2.sProject = 'B') ,
ProjectC = (select dhours from #TEST t2 where t2.sCalldate = t.sCalldate and t2.sProject = 'C')
from
(select distinct sCalldate from #TEST) t
order by t.sCalldate

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ninel
Posting Yak Master

141 Posts

Posted - 2006-04-03 : 13:28:47

How can I do this without hardcoding the projects? New projects are added almost everyday.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-03 : 19:14:48

quote:
Originally posted by ninel

How can I do this without hardcoding the projects? New projects are added almost everyday.

Use Dynamic SQL or use your front end reporting tool to do the cross tab

KH
Choice is an illusion, created between those with power, and those without.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources