locked
Pivot issues RRS feed

  • Question

  • Happy holidays!
    I am having issues using PIVOT. I have never seemed to truly understand the syntax behind it and I know this is a simple question....

    I have a simple table that has 3 columns: DayofWeek (Mon - Fri), Time(8:00AM - 5:00PM for each day), and CallsOffered (This is the aggregate). I just need to pivot on the DayofWeek, so I can have Mon - Friday as column names and I cannot seem to do it correctly.

    The table looks like this...

    Monday - 8:30AM - 1254.52
    Monday - 9:00AM - 546.32
    Tuesday - 8:30AM - 5649.32
    Tuesday - 9:00AM - 654.00
    Wednesday - 9:00AM - 546.32
    Wednesday - 9:00AM - 546.32
    Thursday - 9:00AM - 546.32
    Thursday- 9:00AM - 546.32
    Friday - 9:00AM - 546.32
    Friday - 9:00AM - 546.32


    Any help would be greatly appreciated!

    Dave

    Dave SQL Developer


    • Edited by DaveDB Friday, December 27, 2013 4:22 PM
    Friday, December 27, 2013 4:15 PM

Answers

  • Hi,

    Try like this ,

    DECLARE @tmp TABLE (DayofWeeks VARCHAR(20),Times VARCHAR(10),CallsOffered VARCHAR(10))
    
    INSERT @tmp SELECT 'Monday','8:30AM','1254.52'
    INSERT @tmp SELECT  'Monday','9:00AM','546.32'
    INSERT @tmp SELECT 'Tuesday','8:30AM','5649.32'
    INSERT @tmp SELECT 'Tuesday','9:00AM','654.00'
    INSERT @tmp SELECT 'Wednesday','9:00AM','546.32'
    INSERT @tmp SELECT 'Wednesday','9:00AM','546.32'
    INSERT @tmp SELECT 'Thursday','9:00AM','546.32'
    INSERT @tmp SELECT 'Thursday','9:00AM','546.32'
    INSERT @tmp SELECT 'Friday','9:00AM','546.32'
    INSERT @tmp SELECT 'Friday','9:00AM','546.32'
    
    SELECT * FROM (
    SELECT * FROM @tmp  ) i
    PIVOT( MAX(CallsOffered) FOR DayofWeeks IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday]) ) j


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Marked as answer by DaveDB Friday, December 27, 2013 6:31 PM
    Friday, December 27, 2013 4:39 PM

All replies

  • Create Table #Foo(DayOfWeek varchar(10), Time varchar(12), CallsOffered int);
    Insert #Foo(DayOfWeek, Time, CallsOffered) Values
    ('Monday', '8:30AM', 1254.52),
    ('Monday', '9:00AM', 546.32),
    ('Tuesday', '8:30AM', 5649.32),
    ('Tuesday', '9:00AM', 654.00),
    ('Wednesday', '9:00AM', 546.32),
    ('Wednesday', '9:00AM', 546.32),
    ('Thursday', '9:00AM', 546.32),
    ('Thursday', '9:00AM', 546.32),
    ('Friday', '9:00AM', 546.32),
    ('Friday', '9:00AM', 546.32);
    Select [Monday], [Tuesday], [Wednesday], [Thursday], [Friday]
    From (Select DayOfWeek, CallsOffered From #Foo) As s
    Pivot(Sum(CallsOffered) For DayOfWeek In ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday])) pvt
    
    go
    Drop Table #Foo;

    Tom
    Friday, December 27, 2013 4:35 PM
  • Hi,

    Try like this ,

    DECLARE @tmp TABLE (DayofWeeks VARCHAR(20),Times VARCHAR(10),CallsOffered VARCHAR(10))
    
    INSERT @tmp SELECT 'Monday','8:30AM','1254.52'
    INSERT @tmp SELECT  'Monday','9:00AM','546.32'
    INSERT @tmp SELECT 'Tuesday','8:30AM','5649.32'
    INSERT @tmp SELECT 'Tuesday','9:00AM','654.00'
    INSERT @tmp SELECT 'Wednesday','9:00AM','546.32'
    INSERT @tmp SELECT 'Wednesday','9:00AM','546.32'
    INSERT @tmp SELECT 'Thursday','9:00AM','546.32'
    INSERT @tmp SELECT 'Thursday','9:00AM','546.32'
    INSERT @tmp SELECT 'Friday','9:00AM','546.32'
    INSERT @tmp SELECT 'Friday','9:00AM','546.32'
    
    SELECT * FROM (
    SELECT * FROM @tmp  ) i
    PIVOT( MAX(CallsOffered) FOR DayofWeeks IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday]) ) j


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Marked as answer by DaveDB Friday, December 27, 2013 6:31 PM
    Friday, December 27, 2013 4:39 PM
  • What is the desired output? Do you want to show time of the date as a column in the output and provide row for each time or you want to provide a single row of result? If the latter, then Tom's solution will work, if the former, just keep the time in the inner table, the solution will be the same.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, December 27, 2013 6:14 PM