Sql Server多行合并及分组查询的例子

--创建 test 表 ，插入数据
CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int)
INSERT test SELECT '001', 'aa',1
UNION ALL SELECT '001', 'bb',2
UNION ALL SELECT '002', 'aaa',4
UNION ALL SELECT '002', 'bbb',5
UNION ALL SELECT '002', 'ccc',3;

--方法一
--将多行合并成一行，并做分组统计
SELECT code,
[values] =
stuff(b.[values].value('/R[1]', 'nvarchar(max)'),
,
,
''),[count]
FROM (SELECT code,sum([count]) as [count]
FROM test
GROUP BY code) a
CROSS apply (
SELECT [values] =(
SELECT N',' + [values] FROM test
WHERE code = a.code
FOR XML PATH(''), ROOT('R'), TYPE
)
) b;

--方法二
---sql2005中的新解法 使用XML
SELECT code, data=STUFF((SELECT ','+[values] FROM test t WHERE code=t1.code FOR XML PATH('')), 1, 1, ''),sum([count]) as [count]
FROM test t1
GROUP BY code
--查询结果
--001 aa,bb 3
--002 aaa,bbb,ccc 12
drop table test

SELECT [Source], COUNT(*) AS OrderTotal FROM [ExternalOrder]
Where OrderStatus=1 AND (CheckPayment=1 ) and TicketDate >= '2012-11-1' AND TicketDate < '2012-12-1'
GROUP BY [Source]
WITH ROLLUP Order by [Source] desc

select 年龄,sum(男),sum(女) 
from 
( 
    select 
          case when 年龄<18 then '18以下' else  
          case when 年龄>=18 and 年龄<30 then '18-30' else  
          case when 年龄>=30 and 年龄<40 then '30-40' else  
          case when 年龄>=40 and 年龄<50 then '40-50' else  
          case when 年龄>=50 and 年龄<60 then '50-60' else  
          case when 年龄>=60 then '60以上'  
          else '其他'  end end end end end as 年龄, 
 
          case when 性别='男' then 1 else 0 end as 男, 
 
          case when 性别='女' then 1 else 0 end as 女 
     from 人口信息表 
) as T 
group by T.年龄 
order by T.年龄