How to Combine Rows value based on group by column name

Question

I have the table in below format,

schemecode  TotalValue  DateVal
A                  1    2014-07-09
AA                 1    2014-07-09
AAA                1    2014-09-25
AAB                1    2014-09-24
ABC                1    2014-09-25
ABD                1    2014-08-25

I need the result like below format,

schemecode  TotalValue  DateVal
A,AA             2      2014-07-09
AAA,ABC          2      2014-09-25
AAB              1      2014-09-24
ABD              1      2014-08-25

Thanks in Advance.

grouping by column name would group them by unique columns, your output logic isnt unique? — Matt, Nov 28 '14 at 11:42
same requirement is already available with answer. http://stackoverflow.com/questions/15154644/sql-group-by-to-combine-concat-a-column — Sandeep, Nov 28 '14 at 11:57

score 2 · Accepted Answer · answered Nov 28 '14 at 11:59

Oracle 11g

select listagg(schemecode, ',') within group (order by schemecode) as codes, 
       sum(totalvalue), dateval
from your_table
group by dateval;

Oracle 10g

select wm_concat(schemecode) as codes, 
       sum(totalvalue), dateval
from your_table
group by dateval;

MySQL

select group_concat(schemecode, ',') as codes, 
       sum(totalvalue), dateval
from your_table
group by dateval;

SQL Server (not tested)

select codes = STUFF((
          SELECT ',' + t2.schemecode
          FROM your_table t2
          WHERE t2.dateval = t.dateval
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), 
       sum(t.totalvalue), t.dateval
from your_table t
group by t.dateval;

For the SQL Server it gives error Incorrect syntax near the keyword 'from'. Just FYI :) — Nightmaresux, Nov 28 '14 at 12:09

How to Combine Rows value based on group by column name

1 Answers1