求一汇总语句

[luckljtchinaren]

汇总员工的上下级。
源表：
[备注]Name为员工姓名，RName为上下级姓名，Type表示上下级关系：10为上级,20为下级。
Name RName Type
A A1 10
A A2 10
A A3 20
A A4 20
A A5 20
B B1 10
B B2 20
B B3 20
B B4 20
C C1 20
C C2 20
D D1 10
D D2 10

查询要求1.按员工进行汇总上下级，所有上级为一列，上级之间以顿号分开；所有下级为一列，下级之间以顿号分开。结果形式如下：
Name UpNames DownNames
A　　 A1、A2 A3、A4
B B1 B2、B3、B4
C .... ....
D .... ....

查询要求2.按员工进行汇总上下级，每一上级或下级为一列（假设最多有三个上级、最多有三个下级）。结果形式如下：
Name Up1 Up2　Up3 Down1　Down2　Down3
A　　　A1　　A2　NULL　A3　　A4　　　A5
B　　　　。。。。。。。。。。。。。。。。。

麻烦各位高人了，谢谢:)

[-狙击手-]

create table ta(Name varchar(2),RName varchar(3),Type int)
insert ta select 'A','A1',10 
insert ta select 'A','A2',10 
insert ta select 'A','A3',20 
insert ta select 'A','A4',20 
insert ta select 'A','A5',20 
insert ta select 'B','B1',10 
insert ta select 'B','B2',20 
insert ta select 'B','B3',20 
insert ta select 'B','B4',20 
insert ta select 'C','C1',20 
insert ta select 'C','C2',20 
insert ta select 'D','D1',10 
insert ta select 'D','D2',10 


go

--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID varchar(4),@type int)
returns varchar(40)
as
begin
    declare @ret varchar(40)


        select @ret=','+rtrim(rname)+isnull(@ret,'')
        from
            ta a
        where
            a.Name=@ID and Type=@type

    set @ret=stuff(@ret,1,1,'')
    return @ret
end
go

--执行查询
select distinct name,isnull(dbo.f_getParent(name,10),'') as parentname ,
isnull(dbo.f_getParent(name,20),'') as childname 
from  ta
go

--输出结果
/*
name parentname                               childname                                
---- ---------------------------------------- ---------------------------------------- 
A    A2,A1                                    A5,A4,A3
B    B1                                       B4,B3,B2
C                                             C2,C1
D    D2,D1                                    

（所影响的行数为 4 行）
*/

--删除测试数据
drop function f_getParent
drop table ta
go

[fcuandy]

下面的写法,会弄的你系统当机.hehe

create table tb(name varchar(10),rname varchar(10),type int)
insert into tb select 'A','A1',10
insert into tb select 'A','A2',10
insert into tb select 'A','A3',20
insert into tb select 'A','A4',20
insert into tb select 'A','A5',20
insert into tb select 'B','B1',10
insert into tb select 'B','B2',20
insert into tb select 'B','B3',20
insert into tb select 'B','B4',20
insert into tb select 'C','C1',20
insert into tb select 'C','C2',20
insert into tb select 'D','D1',10
insert into tb select 'D','D2',10
DECLARE @x XML
SET @x=(SELECT * FROM tb FOR XML PATH,TYPE)
SELECT DISTINCT CAST(v.query('./name/text()') AS NVARCHAR(20)),
	CASE WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='A' THEN CAST(v.query('//row[name=''A'']/rname/text()') AS NVARCHAR(100))
		WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='B' THEN CAST(v.query('//row[name=''B'']/rname/text()') AS NVARCHAR(100))
		WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='C' THEN CAST(v.query('//row[name=''C'']/rname/text()') AS NVARCHAR(100))
		WHEN CAST(v.query('./name/text()') AS NVARCHAR(20))='D' THEN CAST(v.query('//row[name=''D'']/rname/text()') AS NVARCHAR(100))
	END
	FROM @x.nodes('//row') AS t(v)

drop table tb

[dawugui]

/*
带符号合并行列转换(2007-11-19于海南三亚)

有表tb，其数据如下：
  a b
  1 1
  1 2
  1 3
  2 1
  2 2
  3 1
如何转换成如下结果:
  a b
  1 1,2,3
  2 1,2
  3 1 
*/

create table tb
(
   a int,
   b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
go

--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(b as varchar) from tb where a = @a 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go

--调用自定义函数得到结果：
select distinct a ,dbo.f_hb(a) as b from tb

drop table tb
drop function f_hb

/*
结果
a           b     
----------- ------
1           1,2,3
2           1,2
3           1

（所影响的行数为 3 行）
*/

----------------------------------------------------
/*
多个前列的合并
数据的原始状态如下：
ID  PR   CON  OP    SC 
001 p    c    差    6
001 p    c    好    2
001 p    c    一般  4
002 w    e    差    8
002 w    e    好    7
002 w    e    一般  1
用SQL语句实现，变成如下的数据
ID  PR   CON  OPS
001 p    c    差(6),好(2),一般(4)
002 w    e    差(8),好(7),一般(1)
*/

create table tb
(
  id  varchar(10),
  pr  varchar(10),
  con varchar(10),
  op  varchar(10),
  sc  int
)
 
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '差',    6)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '好',    2)
insert into tb(ID,PR,CON,OP,SC) values('001', 'p',    'c',    '一般',  4)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '差',    8)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '好',    7)
insert into tb(ID,PR,CON,OP,SC) values('002', 'w',    'e',    '一般',  1)
go

--创建一个合并的函数
create function f_hb(@id varchar(10) , @pr varchar(10) , @con varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(OP as varchar) + '(' 
                           + cast(sc as varchar) + ')' 
  from tb where id = @id and @pr = pr and @con = con
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go

--调用自定义函数得到结果：
select distinct id , pr , con , dbo.f_hb(id,pr,con) as ops from tb

drop table tb
drop function f_hb

/*
结果
id         pr         con        ops               
---------- ---------- ---------- ------------------
001        p          c          差(6),好(2),一般(4)
002        w          e          差(8),好(7),一般(1)

（所影响的行数为 2 行）
*/

----------------------------------------------------
/*如何将一列中所有的值一行显示
数据源
  a
  b
  c
  d
  e
结果
a,b,c,d,e
*/

create table tb(col varchar(20))
insert tb values ('a')
insert tb values ('b')
insert tb values ('c')
insert tb values ('d')
insert tb values ('e')
go

--方法一
declare @sql varchar(1000)
set @sql = ''
select @sql = @sql + t.col + ',' from (select col from tb) as t
set @sql='select result = ''' + @sql + ''''
exec(@sql)
/*
result     
---------- 
a,b,c,d,e,
*/

--方法二
declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + col from tb
print @output
/*
a,b,c,d,e
*/

drop table tb

[pt1314917]

1\create table tb(name varchar(10),rname varchar(10),type int)
insert into tb select 'A','A1',10
insert into tb select 'A','A2',10
insert into tb select 'A','A3',20
insert into tb select 'A','A4',20
insert into tb select 'A','A5',20
insert into tb select 'B','B1',10
insert into tb select 'B','B2',20
insert into tb select 'B','B3',20
insert into tb select 'B','B4',20
insert into tb select 'C','C1',20
insert into tb select 'C','C2',20
insert into tb select 'D','D1',10
insert into tb select 'D','D2',10

create function sdfs(@name varchar(10),@type int)
returns varchar(1000)
as
begin
	declare @sql varchar(1000)
	select @sql=isnull(@sql+',','')+rname from tb where name=@name and type=@type
	return @sql
end

select distinct name,dbo.sdfs(name,10),dbo.sdfs(name,20) from tb

[dawugui]

create table tb(Name varchar(10),RName varchar(10),Type int)
insert into tb values('A',             'A1',         10 )
insert into tb values('A',             'A2',         10 )
insert into tb values('A',             'A3',         20 )
insert into tb values('A',             'A4',         20 )
insert into tb values('A',             'A5',         20 )
insert into tb values('B',             'B1',         10 )
insert into tb values('B',             'B2',         20 )
insert into tb values('B',             'B3',         20 )
insert into tb values('B',             'B4',         20 )
insert into tb values('C',             'C1',         20 )
insert into tb values('C',             'C2',         20 )
insert into tb values('D',             'D1',         10 )
insert into tb values('D',             'D2',         10 )
go
--创建一个合并的函数
create function f_hb(@name varchar , @type int)
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(RName as varchar) from tb where name = @name and type = @type
  set @str = right(@str , len(@str))
  return(@str)
End
go

--调用自定义函数得到结果：
select name , 
       upnames = case when left(upnames,1) = ',' and len(upnames)>0 then right(upnames,len(upnames)-1) else '' end ,
       DownNames = case when left(DownNames,1) = ',' and len(DownNames)>0 then right(DownNames,len(DownNames)-1) else '' end 
from
(
  select distinct name ,dbo.f_hb(name,10) as UpNames , dbo.f_hb(name,20) as DownNames from tb
) t

drop table tb
drop function f_hb

/*
name       upnames                                                                                                                                                                                                                                                          DownNames                                                                                                                                                                                                                                                        
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
A          A1,A2                                                                                                                                                                                                                                                            A3,A4,A5
B          B1                                                                                                                                                                                                                                                               B2,B3,B4
C                                                                                                                                                                                                                                                                           C1,C2
D          D1,D2                                                                                                                                                                                                                                                            

（所影响的行数为 4 行）

*/

[luckljtchinaren]

果然是高手如云阿！
问题已经根据答复解决了，非常感谢！
让又多了用自定义函数的思路，以前虽然了解但还没有自已创建过自定义函数。
不过调用函数，循环执行自定义函数的select语句，使用得效率相对慢了点，我本地查询分析器中执行要十几二十秒。
不知道有没有更快的语句，因为要放到程序中的话，十几二十秒可能会出现超时异常。
再次谢谢大家的帮助:)
Best regards
lucky

[pt1314917]

加索引试试。
函数已经无法优化了，，顶多根据实际需求，把函数返回值大小改小一点。现在是1000，如果楼主数据没那么多，
可以改成100或其他。
主要还是加索引
create function sdfs(@name varchar(10),@type int)
returns varchar(100)
as
begin
    declare @sql varchar(100)
    select @sql=isnull(@sql+',','')+rname from tb where name=@name and type=@type
    return @sql
end