locked
Combining multiple columns and rows into one (sort of !) RRS feed

  • Question

  • User2079058187 posted

    I am getting following result for a query:

    ID     DAY_NAME     TOTAL
    1      Mon          2
    1      Tue          5
    1      Wed          1
    1      Thu          5
    1      Fri          0
    1      Sat          0
    1      Sun          4
    2      Mon          7
    2      Tue          1
    2      Wed          1
    2      Thu          0
    2      Fri          2
    2      Sat          5
    2      Sun          0
    
    

    I am looking to combine the above rows into something like the following:

    ID     DAY_NAMES               TOTAL
    1      Mon 2, Tue 5, Wed 1...  17
    2      Mon 7, Tue 1, Wed 1...  16
    
    

    Thanks

    Friday, December 7, 2012 5:59 AM

All replies

  • User551462331 posted

    try this

    Declare @temp as varchar(1000)
    
    select ID, (case when @temp is null
                then ''
               else @temp + ',' end) + Total as Day_Names, Total
    from TableName

    hope this helps...

    Friday, December 7, 2012 6:53 AM
  • User2079058187 posted

    Where is @temp assigned the day name? Or does it not have to?

    And also,  the Total column should be a total of all 7 days.

    Friday, December 7, 2012 10:46 AM
  • User2079058187 posted

    I'm using MySql, can anyone help with this?

    Saturday, December 8, 2012 1:48 AM