这里寻求高手指点
IDX CODE DEPTCODE PARTNAME
--------------------
1 CN00001 CN00001001 PART1
2 CN00001 CN00001002 PART2
3 CN00001 CN00001001001 PART3
4 CN00001 CN00001002001 PART4
5 CN00002 CN00002001 PART5
6 CN00002 CN00002001001 PART6
IDX CODE DEPTCODE DETAIL
-----------------------------------------------
1 CN00001 CN0001001 CN0001001;PART1|CN0001001001;PART3
2 CN00001 CN0001002 CN0001002;PART2|CN0001002001;PART4
3 CN00002 CN0002001 CN0002001;PART5|CN0002001001;PART6
DEPTCODE是部门代码，规则是CODE+3位为第一级部门，以此类推，下级部门在上级部门代码上加3位
我想要的结果是只是列出第一级部门信息，下级部门信息归类成为DETAIL字段，请大侠指点；
[解决办法]
这里有个列子:
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx
[解决办法]
是定义函数行列转换:
例子:
--有表t，其数据如下：
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
--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1
[解决办法]
create table T(IDX int, CODE varchar(50), DEPTCODE varchar(50), PARTNAME varchar(50))
insert T select 1, 'CN00001 ', 'CN00001001 ' , 'PART1 '
union all select 2, 'CN00001 ', 'CN00001002 ', 'PART2 '
union all select 3, 'CN00001 ', 'CN00001001001 ', 'PART3 '
union all select 4, 'CN00001 ', 'CN00001002001 ', 'PART4 '
union all select 5, 'CN00002 ', 'CN00002001 ', 'PART5 '
union all select 6, 'CN00002 ', 'CN00002001001 ', 'PART6 '
create function fun(@DEPTCODE varchar(50))
returns varchar(200)
as
begin
declare @re varchar(200)
set @re= ' '
select @re=@re+DEPTCODE+ '; '+PARTNAME+ '| ' from T where DEPTCODE like @DEPTCODE+ '% '
select @re=left(@re, len(@re)-1)
return @re
end
select IDX, CODE, DEPTCODE, DETAIL=dbo.fun(DEPTCODE) from T where len(DEPTCODE)=10
--result
IDX CODE DEPTCODE DETAIL
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------
1 CN00001 CN00001001 CN00001001;PART1|CN00001001001;PART3
2 CN00001 CN00001002 CN00001002;PART2|CN00001002001;PART4
5 CN00002 CN00002001 CN00002001;PART5|CN00002001001;PART6
(3 row(s) affected)
[解决办法]
如果只有一层
select CODE,DEPTCODE,DETAIL=(DEPTCODE+ '; '+PARTNAME+ ' | '+(select DEPTCODE+ '; '+PARTNAME from # where left(DEPTCODE,10)=t.DEPTCODE and len(DEPTCODE)=13)) from # t where len(DEPTCODE)=10
--result
CN00001CN00001001CN00001001;PART1 | CN00001001001;PART3
CN00001CN00001002CN00001002;PART2 | CN00001002001;PART4
CN00002CN00002001CN00002001;PART5 | CN00002001001;PART6
[解决办法]
create table ta(IDX int, CODE varchar(50), DEPTCODE varchar(50), PARTNAME varchar(50))
insert ta select 1, 'CN00001 ', 'CN00001001 ' , 'PART1 '
union all select 2, 'CN00001 ', 'CN00001002 ', 'PART2 '
union all select 3, 'CN00001 ', 'CN00001001001 ', 'PART3 '
union all select 4, 'CN00001 ', 'CN00001002001 ', 'PART4 '
union all select 5, 'CN00002 ', 'CN00002001 ', 'PART5 '
union all select 6, 'CN00002 ', 'CN00002001001 ', 'PART6 '
create function test_f(@DEPTCODE varchar(50))
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s, ' ')+DEPTCODE+ ': '+PARTNAME+ '| ' from ta where charindex(@DEPTCODE,DEPTCODE)=1
return @s
end
select IDX,CODE, 显示=dbo.test_f(DEPTCODE) from ta where len(dbo.test_f(DEPTCODE))> 21
IDX CODE 显示
----------- -------------------------------------------------- ----------------------------------------------------------------
1 CN00001 CN00001001:PART1|CN00001001001:PART3|
2 CN00001 CN00001002:PART2|CN00001002001:PART4|
5 CN00002 CN00002001:PART5|CN00002001001:PART6|
（所影响的行数为 3 行）
[解决办法]
insert ta select 1, 'CN00001 ', 'CN00001001 ' , 'PART1 '
union all select 2, 'CN00001 ', 'CN00001002 ', 'PART2 '
union all select 3, 'CN00001 ', 'CN00001001001 ', 'PART3 '
union all select 4, 'CN00001 ', 'CN00001002001 ', 'PART4 '
union all select 5, 'CN00002 ', 'CN00002001 ', 'PART5 '
union all select 6, 'CN00002 ', 'CN00002001001 ', 'PART6 '
改一下:
create function test_f(@DEPTCODE varchar(50))
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s, ' ')+DEPTCODE+ ': '+PARTNAME+ '| ' from ta where charindex(@DEPTCODE,DEPTCODE)=1
return left(@s,len(@s)-1)
end
select IDX,CODE,DEPTCODE ,显示=dbo.test_f(DEPTCODE) from ta where len(dbo.test_f(DEPTCODE))> 21
1CN00001CN00001001CN00001001:PART1|CN00001001001:PART3
2CN00001CN00001002CN00001002:PART2|CN00001002001:PART4
5CN00002CN00002001CN00002001:PART5|CN00002001001:PART6
