高手帮忙写一条SQL语句(已附表结构)

[hooyes]

DECLARE @hooyes TABLE
    (
      area_name VARCHAR(50) ,
      area_code VARCHAR(20)
    )
    
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '1001', 'CA' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '1002', 'CA' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '1003', 'CA' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '2001', 'US' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '4001', 'AU' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '4002', 'AU' )


SELECT  area_name ,
        area_code
FROM    @hooyes
/*
直接查询的结果：


area_name	area_code
CA	        1001
CA	        1002
CA	        1003
US	        2001
AU	        4001
AU	        4002
*/

/*  

想要一条 SQl 查出如下结果,按area_name分组,并且合并 area_code,以逗号隔开。

area_name	area_code
CA	        1001,1002,1003
US	        2001
AU	        4001,4002

 */

[yoan2014]

SELECT  area_name ,
        STUFF((SELECT ',' + b.area_code FROM @hooyes AS b WHERE b.area_name = a.area_name FOR XML PATH('') ) ,1,1,'')
FROM    @hooyes AS a 
GROUP BY area_name

--
area_name                                          
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AU                                                 4001,4002
CA                                                 1001,1002,1003
US                                                 2001

(3 row(s) affected)

[昵称90天可改一次]

[code=sql]
DECLARE @hooyes TABLE
    (
      area_name VARCHAR(50) ,
      area_code VARCHAR(20)
    )
     
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '1001', 'CA' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '1002', 'CA' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '1003', 'CA' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '2001', 'US' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '4001', 'AU' )
INSERT  INTO @hooyes
        ( area_code, area_name )
VALUES  ( '4002', 'AU' )
 
 
select area_name,
	STUFF((SELECT',' + area_code FROM @hooyes WHERE area_name = a.area_name FOR XML PATH('')),1, 1, '')
from @hooyes a
group by area_name

/*
AU	4001,4002
CA	1001,1002,1003
US	2001
*/

[/code]

[lzw_0736]

select DISTINCT a.area_name,b.area_code
from @hooyes a 
CROSS APPLY (SELECT STUFF((SELECT',' + area_code FROM @hooyes WHERE area_name = a.area_name FOR XML PATH('')),1, 1, '') area_code) b