Combining multiple columns and rows into one (sort of !) RSS

• • Reply
  

  KashifB

  Member

  503 Points

  403 Posts

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

  Dec 07, 2012 09:59 AM|LINK

  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
  
  

  www.LinkToIslam.net
  • Edited by KashifB on Dec 07, 2012 10:00 AM
• • Reply
  

  kedarrkulkar...

  All-Star

  34013 Points

  5468 Posts

  Re: Combining multiple columns and rows into one (sort of !)

  Dec 07, 2012 10:53 AM|LINK

  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...

  Cheers!
  KK
  
  Please mark as Answer if post helps in resolving your issue
  My Site
• • Reply
  

  KashifB

  Member

  503 Points

  403 Posts

  Re: Combining multiple columns and rows into one (sort of !)

  Dec 07, 2012 02:46 PM|LINK

  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.

  www.LinkToIslam.net
• • Reply
  

  KashifB

  Member

  503 Points

  403 Posts

  Re: Combining multiple columns and rows into one (sort of !)

  Dec 08, 2012 05:48 AM|LINK

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

  www.LinkToIslam.net