SQL Server Performance

Pivot table without using Aggregiate function

Discussion in 'SQL Server 2008 General Developer Questions' started by baburk, Mar 23, 2009.

  1. baburk New Member

    DECLARE @Sample table
    (
    ID VARCHAR(50),
    [Month] VARCHAR(20),
    TotalHours VARCHAR(12),
    [Year] INTEGER
    )



    INSERT @Sample


    SELECT 'WE04', 'March', '52:22:00', 2008 UNION ALL
    SELECT 'WE04', 'APRIL', '62:18:00', 2009 UNION ALL
    SELECT 'WE05', 'March', '45:40:00', 2008 UNION ALL
    SELECT 'WE05', 'APRIL', '70:16:00', 2009 UNION ALL

    SELECT 'WE04', 'January', '52:22:00', 2008 UNION ALL
    SELECT 'WE04', 'June', '62:18:00', 2009 UNION ALL
    SELECT 'WE05', 'February', '45:40:00', 2008 UNION ALL
    SELECT 'WE05', 'October', '70:16:00', 2009 UNION ALL

    SELECT 'WE04', 'February', '52:22:00', 2008 UNION ALL
    SELECT 'WE04', 'October', '62:18:00', 2009 UNION ALL
    SELECT 'WE05', 'January', '45:40:00', 2008 UNION ALL
    SELECT 'WE05', 'June', '70:16:00', 2009



    SELECT [YEAR], ID,
    CASE WHEN [Month] = 'January' THEN TotalHours ELSE NULL END AS 'Jan',
    CASE WHEN [Month] = 'February' THEN TotalHours ELSE NULL END AS 'Feb',
    CASE WHEN [Month] = 'March' THEN TotalHours ELSE NULL END AS 'Mar',
    CASE WHEN [Month] = 'April' THEN TotalHours ELSE NULL END AS 'Apr',
    CASE WHEN [Month] = 'May' THEN TotalHours ELSE NULL END AS 'May',
    CASE WHEN [Month] = 'June' THEN TotalHours ELSE NULL END AS 'Jun',
    CASE WHEN [Month] = 'July' THEN TotalHours ELSE NULL END AS 'Jul',
    CASE WHEN [Month] = 'August' THEN TotalHours ELSE NULL END AS 'Aug',
    CASE WHEN [Month] = 'September' THEN TotalHours ELSE NULL END AS 'Sep',
    CASE WHEN [Month] = 'October' THEN TotalHours ELSE NULL END AS 'Oct',
    CASE WHEN [Month] = 'November' THEN TotalHours ELSE NULL END AS 'Nov',
    CASE WHEN [Month] = 'December' THEN TotalHours ELSE NULL END AS 'Dec'

    FROM @Sample
    GROUP BY [YEAR], ID, [Month], TotalHours
    order by [YEAR], ID
    The output is like this.
    YEAR ID Jan Feb Mar Apr May Jun Jul
    ----------- ------- --------- ------------ ------------ ------------ ------------ ------------ ------------
    2008 WE04 NULL 52:22:00 NULL NULL NULL NULL NULL
    2008 WE04 52:22:00 NULL NULL NULL NULL NULL NULL
    2008 WE04 NULL NULL 52:22:00 NULL NULL NULL NULL
    2008 WE05 NULL 45:40:00 NULL NULL NULL NULL NULL
    2008 WE05 45:40:00 NULL NULL NULL NULL NULL NULL
    2008 WE05 NULL NULL 45:40:00 NULL NULL NULL NULL
    2009 WE04 NULL NULL NULL 62:18:00 NULL NULL NULL
    2009 WE04 NULL NULL NULL NULL NULL NULL 62:18:00
    2009 WE04 NULL NULL NULL NULL NULL 62:18:00 NULL
    2009 WE05 NULL NULL NULL 70:16:00 NULL NULL NULL
    2009 WE05 NULL NULL NULL NULL NULL NULL 70:16:00
    2009 WE05 NULL NULL NULL NULL NULL 70:16:00 NULL
    But I want it in a single row like this
    YEAR ID Jan Feb Mar Apr May Jun Jul
    ----------- ------- --------- ------------ ------------ ------------ ------------ ------------ ------------
    2008 WE04 52:22:00 52:22:00 52:22:00 NULL NULL NULL NULL

    2008 WE05 45:40:00 45:40:00 45:40:00 NULL NULL NULL NULL

    2009 WE04 NULL NULL NULL 62:18:00 NULL 62:18:00 62:18:00

    2009 WE05 NULL NULL NULL 70:16:00 NULL 70:16:00 70:16:00
    This also didn't help me.(I don't want to use any aggregate function)
    SELECT [YEAR] ,
    ID ,
    [January] ,
    [February] ,
    [March] ,
    [April] ,
    [May] ,
    [June] ,
    [July] ,
    [August] ,
    [September],
    [October] ,
    [November] ,
    [December]
    FROM @Sample pivot (min(TotalHours) FOR TotalHours IN ([January] ,
    [February] ,
    [March] ,
    [April] ,
    [May] ,
    [June] ,
    [July] ,
    [August] ,
    [September],
    [October] ,
    [November] ,
    [December])) AS Mon

    Thanks
    baburk, Mar 23, 2009
    #2

  2. satya Moderator

    You could also cross tab this without using pivot.
    satya, Mar 24, 2009
    #3

  3. FrankKalis Moderator

    [quote user="satya"]You could also cross tab this without using pivot.
    [/quote]
    Yes, but then he would have to use aggregate functions. [:)]
    FrankKalis, Mar 24, 2009
    #4

  4. satya Moderator

    Doh, forgot that and I would like to know why OP doesn't want to use Aggregate func [:)]
    TA
    satya, Mar 24, 2009
    #5

  5. baburk New Member


    baburk, Mar 24, 2009
    #6

  6. FrankKalis Moderator

    [quote user="baburk"]

    [/quote]
    Aha?!? [:)]
    FrankKalis, Mar 24, 2009
    #7

  7. Madhivanan Moderator

    Why do you want not to use aggregate?
    SELECT [YEAR], ID,
    MAX(CASE WHEN [Month] = 'January' THEN TotalHours ELSE NULL END) AS 'Jan',
    MAX(CASE WHEN [Month] = 'February' THEN TotalHours ELSE NULL END) AS 'Feb',
    MAX(CASE WHEN [Month] = 'March' THEN TotalHours ELSE NULL END) AS 'Mar',
    MAX(CASE WHEN [Month] = 'April' THEN TotalHours ELSE NULL END) AS 'Apr',
    MAX(CASE WHEN [Month] = 'May' THEN TotalHours ELSE NULL END) AS 'May',
    MAX(CASE WHEN [Month] = 'June' THEN TotalHours ELSE NULL END) AS 'Jun',
    MAX(CASE WHEN [Month] = 'July' THEN TotalHours ELSE NULL END) AS 'Jul',
    MAX(CASE WHEN [Month] = 'August' THEN TotalHours ELSE NULL END) AS 'Aug',
    MAX(CASE WHEN [Month] = 'September' THEN TotalHours ELSE NULL END) AS 'Sep',
    MAX(CASE WHEN [Month] = 'October' THEN TotalHours ELSE NULL END) AS 'Oct',
    MAX(CASE WHEN [Month] = 'November' THEN TotalHours ELSE NULL END) AS 'Nov',
    MAX(CASE WHEN [Month] = 'December' THEN TotalHours ELSE NULL END) AS 'Dec'FROM @Sample
    GROUP BY [YEAR], ID, TotalHours
    order by [YEAR], ID
    Madhivanan, Mar 26, 2009
    #8

  8. Adriaan New Member

    Have fun writing the code to loop through a cursor and accumulate the data, inserting it into a temp table or a table variable, then returning the aggregate rows from there.
    One reason why there is such a thing as an aggregate query is that it saves you a lot of needless programming.
    Adriaan, Mar 26, 2009
    #9

Share This Page