Cross Tab Query

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.

code:

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
Message posted via http://www.webservertalk.comTry:
select
sCallDate
, sum (case when sProject = 'A' then dHours else 0 end) ProjectA
, sum (case when sProject = 'B' then dHours else 0 end) ProjectB
, sum (case when sProject = 'C' then dHours else 0 end) ProjectC
from
#TEST
group by
sCallDate
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ngorbunov via webservertalk.com" <
u9125@.uwe>
wrote in message
news:5e3bc0ad31e3a@.uwe...
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.

code:

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
Message posted via http://www.webservertalk.com|||--2000
SELECT
sCallDate
,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
FROM #TEST
GROUP BY sCallDate
--2005
SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
FROM #TEST
PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ngorbunov via webservertalk.com" <
u9125@.uwe>
wrote in message news:5e3bc0ad31e3a@.uwe...[color
=darkred]
>
I have a table called summary. This table contains calldate, projects, hour
s.
>
I need to create a report/table that shows the date down the left column a
nd
>
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.
>
>

code:

>
 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
>
>
--
>
Message posted via http://www.webservertalk.com[/color]|||How can I do this without hardcoding the projects? New projects are added
almost everyday.
Tibor Karaszi wrote:
>--2000
>SELECT
> sCallDate
>,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
>,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
>,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
>FROM #TEST
>GROUP BY sCallDate
>--2005
>SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
>FROM #TEST
>PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
>[quoted text clipped - 44 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||SELECT SCALLDATE, SUM([PROJECT A]) AS [PROJECT A], SUM([PROJECT B]) AS
[PROJECT B], SUM([PROJECT C]) AS [PROJECT C]FROM
(
SELECT SCALLDATE,
CASE WHEN SPROJECT = 'A' THEN DHOURS ELSE 0 END AS [PROJECT A] ,
CASE WHEN SPROJECT = 'B' THEN DHOURS ELSE 0 END AS [PROJECT B] ,
CASE WHEN SPROJECT = 'C' THEN DHOURS ELSE 0 END AS [PROJECT C]
FROM #TEST
)AS A
GROUP BY SCALLDATE|||Unfortunately, you'd have to use dynamic SQL to generate the statement and
then execute the statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message
news:5e3bec27d6652@.uwe...
How can I do this without hardcoding the projects? New projects are added
almost everyday.
Tibor Karaszi wrote:
>--2000
>SELECT
> sCallDate
>,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
>,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
>,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
>FROM #TEST
>GROUP BY sCallDate
>--2005
>SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
>FROM #TEST
>PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
>[quoted text clipped - 44 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||Try this,,
select sCalldate, ProjectA = ISNULL ((SELECT dHours FROM #Test WHERE
sProject = 'A' AND sCalldate =
Q.sCallDate),0),
ProjectB = ISNULL ((SELECT dHours FROM #Test WHERE sProject = 'B' AND
sCalldate =
Q.sCallDate),0),
ProjectC = ISNULL ((SELECT dHours FROM #Test WHERE sProject = 'C' AND
sCalldate =
Q.sCallDate),0)
FROM #test Q
GROUP BY sCallDate
Ref : EN-US;
q175574" target="_blank">http://support.microsoft.com/defaul...b;
EN-US;
q175574
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"ngorbunov via webservertalk.com" wrote:

>
I have a table called summary. This table contains calldate, projects, hou
rs.
>
I need to create a report/table that shows the date down the left column a
nd
>
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.
>
>

code:

>
 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
>
>
--
>
Message posted via http://www.webservertalk.com
>
|||Thats really informative sql 2005 way of doing cross tab.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Tibor Karaszi" wrote:

> --2000
> SELECT
> sCallDate
> ,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
> ,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
> ,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
> FROM #TEST
> GROUP BY sCallDate
> --2005
> SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
> FROM #TEST
> PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message news:5e3bc0ad3
1e3a@.uwe...
>|||Hi,
Check out the RAC utility.It will easily do what you want and
will not insult your intelligence:)
www.rac4sql.net
"ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message
news:5e3bec27d6652@.uwe...
> How can I do this without hardcoding the projects? New projects are added
> almost everyday.
> Tibor Karaszi wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||How much does it take for your intelligence to be insulted?
Over 5 years in development and an army to do it and this is what
they throw at users':)
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:B53C964A-F511-451D-8597-BF21640130BA@.microsoft.com...
> Thats really informative sql 2005 way of doing cross tab.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Tibor Karaszi" wrote:
>