CODEWITHSUNDEEP
sql
PDP
PDP

Reputation: 59

How to Combine Rows value based on group by column name

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.

Upvotes: 0

Views: 110

Answers (1)

Multisync
Multisync

Reputation: 8797

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;

Upvotes: 2

Related Questions