SQL中有一张表，如下：
dep code
A 0001
A 0002
A 0003
B 0004
B 0005
C 0006
如何用SQl语句将上表转换成如下格式呈现？
dep code count
A 0001,0002,0003 3
B 0004,0005 2
C 0006 1
……
dep数据会有很多，不能直接固定写死，请大神赐教!
------解决思路----------------------
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-05 16:36:18
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([dep] varchar(1),[code] varchar(4))
insert [huang]
select 'A','0001' union all
select 'A','0002' union all
select 'A','0003' union all
select 'B','0004' union all
select 'B','0005' union all
select 'C','0006'
--------------开始查询--------------------------
select a.[dep],
stuff((select ','+[code] from [huang] b
where b.[dep]=a.[dep]
for xml path('')),1,1,'') [code],COUNT(1) [count]
from [huang] a
group by a.[dep]
----------------结果----------------------------
/*
dep code count
---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
A 0001,0002,0003 3
B 0004,0005 2
C 0006 1
*/
------解决思路----------------------
2000还是2005以上 2005以上用1楼的 2000的话用函数。
------解决思路----------------------
select a.dept,
cast((select ','+code from tbname b
where a.dept = b.dept
for xml path('')),1,1,'')[code],count(1)[code]
from tbname a
group by a.dept
------解决思路----------------------
http://bbs.csdn.net/topics/260012838