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.

    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

     

  • 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

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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

  • 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