如何根据列名称分组合并行值

Question

>我有以下格式的表格，

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

我需要如下格式的结果，

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

提前谢谢。

Answer 1

Oracle 11g

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

甲骨文 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（未测试）

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;