分组统计SQL语句?

[long7411]

表A:
市县名 乡镇名

东方 八所镇
东方 天安乡
东方 三家乡
乐东 千家镇
乐东 九所镇
陵水 黎安镇
.....


改成这样的表B:
市县名 乡镇名
东方 八所镇 ,天安乡 ,三家乡
乐东 千家镇 ,九所镇
陵水 黎安镇 .
.......

[kncuba]

if object_id('#tableName')is not null 

drop table #tableName

go

create table #tableName ([市县名]varchar(20),[乡镇名]varchar(20))

insert into #tableName values('东方','八所镇')

insert into #tableName values('东方','天安乡')

insert into #tableName values('东方','三家乡')

insert into #tableName values('乐东','千家镇')

insert into #tableName values('乐东','九所镇')

insert into #tableName values('陵水','黎安镇')

go



SELECT B.[市县名],LEFT(List,LEN(List)-1) as [乡镇名] FROM (
SELECT [市县名],
(SELECT [乡镇名]+',' FROM #tableName 
  WHERE [市县名]=A.[市县名] 
  FOR XML PATH('')) AS List
FROM #tableName A 
GROUP BY [市县名]
) B 

[Pact_Alice]

WITH cet AS ( SELECT '东方' AS NAME ,'八所镇' AS ADDRESS UNION ALL SELECT '东方' ,'天安乡' UNION ALL SELECT '陵水' ,'黎安镇' ) SELECT c.NAME ,(STUFF((SELECT ','+c1.ADDRESS FROM cet c1 WHERE c1.name=c.name FOR XML PATH ('') ),1,1,''))AS ADDRESS FROM cet c GROUP BY c.NAME