sql有关问题 求大手帮忙额

use DBTestgoif OBJECT_ID('tabTest') is not null drop table tabTestgocreate table tabTest(Province int,City int,Area int,AreaName nvarchar(50),UserName nvarchar(50))insert into tabTestselect 13,1301,130100,'市局1','测试员1' union allselect 13,1301,130100,'市局1','测试员2' union allselect 13,1301,130101,'第一分局','测试员2' union allselect 13,1302,130200,'市局2','测试员4' union allselect 13,1302,130200,'市局2','测试员4'gocreate function GetNames(@Province int,@City int,@Area int,@AreaName nvarchar(50))returns nvarchar(800)asbegindeclare   @s   nvarchar(800) set   @s= '' select   @[email protected] + ','+UserName   from   tabTest  where   [email protected] and [email protected] and [email protected] and [email protected]return    substring(@s,2,len(@s)-1)endgoSELECT Province,City,Area,AreaName,dbo.GetNames(Province,City,Area,AreaName) as NamesFROM tabTestgroup by Province,City,Area,AreaName
------解决方案--------------------
SQL code
--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([省] int,[市] int,[县区] int,[县区名称] varchar(8),[人员] varchar(7))insert [tbl]select 13,1301,130100,'市局1','测试员1' union allselect 13,1301,130100,'市局1','测试员2' union allselect 13,1301,130101,'第一分局','测试员3' union allselect 13,1302,130200,'市局2','测试员4' union allselect 13,1302,130200,'市局2','测试员5'SELECT *FROM (SELECT DISTINCT [省],[市],[县区],[县区名称] FROM tbl)AOUTER APPLY(    SELECT [人员]= STUFF(REPLACE(REPLACE(            (   SELECT [人员] FROM tbl N                WHERE [省]= A.[省] AND [市]=A.市 AND [县区]=A.县区 AND [县区名称]=A.县区名称                FOR XML AUTO             ), '<N 人员="', ' '), '"/>', ''), 1, 1, ''))N/*省    市    县区    县区名称    人员13    1301    130100    市局1    测试员1 测试员213    1301    130101    第一分局    测试员313    1302    130200    市局2    测试员4 测试员5*/
------解决方案--------------------
SQL code
create table [tb](cola varchar(8),colb varchar(8),colc varchar(8),cold varchar(8),cole varchar(7))insert [tb]select '13','1301','130100','市局1','测试员1' union allselect '13','1301','130100','市局1','测试员2' union allselect '13','1301','130101','第一分局','测试员3' union allselect '13','1302','130200','市局2','测试员4' union allselect '13','1302','130200','市局2','测试员5'goselect *,rid=identity(int,1,1) into #tbfrom tborder by cola,colb,colc,coldselect (case when (select count(*) from #tb where cola=t.cola and rid<=t.rid)=1 then cola else '' end) cola,    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and rid<=t.rid)=1 then colb else '' end) colb,    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and colc=t.colc and rid<=t.rid)=1 then colc else '' end) colc,    (case when (select count(*) from #tb where cola=t.cola and colb=t.colb and colc=t.colc and cold=t.cold and rid<=t.rid)=1 then cold else '' end) cold,    colefrom #tb tdrop table tb,#tb/*****************************************cola     colb     colc     cold     cole-------- -------- -------- -------- -------13       1301     130100   市局1     测试员1                                     测试员2                  130101   第一分局  测试员3         1302     130200   市局2     测试员4                                     测试员5(5 行受影响)
------解决方案--------------------
SQL code