April 3, 2006 at 11:10 am
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
April 3, 2006 at 11:21 am
select sCallDate as 'Date',
SUM(Case sProject when 'A' then dHours else 0 end) as 'Project A',
SUM(Case sProject when 'B' then dHours else 0 end) as 'Project B',
SUM(Case sProject when 'C' then dHours else 0 end) as 'Project C'
FROM #test
group by sCallDate
April 3, 2006 at 11:27 am
Thank you so much for your help. That solved half of my issue. It turns out that the projects are created all the time. Is there a way to do this without hardcoding the project code?
Thanks,
Ninel
April 3, 2006 at 3:34 pm
Here is a possible way to do it dynamically so you do not have to hardcode the Project Code:
(chances are good you can do this more efficiently through an UDF, but I had this one laying around in my notes...)
CREATE TABLE #Test( sCallDate varchar(20) NULL,
sProject varchar(20) NULL,
dHours numeric(10,4) NULL)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060401', 'A', 12)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060401', 'B', 0)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060401', 'C', 2)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060402', 'A', 2)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060402', 'B', 5)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060402', 'C', 4)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060403', 'A', 3)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060403', 'B', 5)
INSERT INTO #Test( sCallDate, sProject, dHours)
VALUES( '20060403', 'C', 1)
CREATE TABLE #Final( CallDate varchar(20))
INSERT INTO #Final( CallDate)
SELECT DISTINCT sCalldate FROM #Test
DECLARE @sql varchar(8000),
@CurrentProject varchar(1),
@MaxProject varchar(1)
SELECT @CurrentProject = (SELECT MIN( sProject) FROM #Test),
@MaxProject = (SELECT MAX( sProject) FROM #Test)
WHILE @CurrentProject <= @MaxProject
BEGIN
SET @sql = 'ALTER TABLE #Final ADD Project' + @CurrentProject + ' numeric(10,4) DEFAULT 0 ' + CHAR(10)
EXEC( @sql)
SET @sql = 'UPDATE #Final SET ' + CHAR(10) + ' Project' + @CurrentProject + ' = T.dHours ' + CHAR(10) +
'FROM #Final F ' + CHAR(10) + ' INNER JOIN #Test T ON( T.sProject = '
+ CHAR(39) + @CurrentProject + CHAR(39) + CHAR(10) +
' AND F.CallDate = T.sCalldate) ' + CHAR(10)
EXEC( @sql)
SET @CurrentProject = (SELECT MIN( sProject) FROM #Test WHERE sProject > @CurrentProject)
END
SELECT * FROM #Final
DROP TABLE #Final
DROP TABLE #Test
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply