Seek expert advice here

unnamed11

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 is the department code, the rule is that CODE + 3 digits are the first-level department, and so on, the lower-level department adds 3 digits to the upper-level department code
The result I want is just to list the first-level department information, and the lower-level department information is categorized into a DETAIL field. Please give me pointers.

unnamed11

Above is the original data, below is the desired result,

There are infinitely many levels in theory.

我是杀人狂

Follow, learn

我是杀人狂

Ideas; define process rank conversion ~

我是杀人狂

Is the definition of the function row and column conversion:
example:
-There is a table t whose data is as follows:
  a b
  1 1
  1 2
  1 3
  twenty one
  twenty two
  3 1
--How to convert into the following results:
  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 a merged function
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

--Call the custom function to get the result:
select distinct a, dbo.f_hb (a) as b from tb

drop table tb

--result
a b
----------- ------
1 1,2,3
2 1,2
3 1

fanfan1980

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)
Ranch
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)

prajna

If there is only one layer
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

CN00001 CN00001001 CN00001001; PART1 | CN00001001001; PART3
CN00001 CN00001002 CN00001002; PART2 | CN00001002001; PART4
CN00002 CN00002001 CN00002001; PART5 | CN00002001001; PART6

unnamed11

fanfan1980()
This method is good, I will try it out and post it later, thank you all,


There are good ways to look forward to it;

恶魔右指

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, show = dbo.test_f (DEPTCODE) from ta where len (dbo.test_f (DEPTCODE))> 21

IDX CODE display
----------- --------------------------------------- ----------- --------------------------------------- -------------------------------------------------- -----------
1 CN00001 CN00001001: PART1 | CN00001001001: PART3 |
2 CN00001 CN00001002: PART2 | CN00001002001: PART4 |
5 CN00002 CN00002001: PART5 | CN00002001001: PART6 |

(3 rows affected)

恶魔右指

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'

Change it:
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, show = dbo.test_f (DEPTCODE) from ta where len (dbo.test_f (DEPTCODE))> 21
1 CN00001 CN00001001 CN00001001: PART1 | CN00001001001: PART3
2 CN00001 CN00001002 CN00001002: PART2 | CN00001002001: PART4
5 CN00002 CN00002001 CN00002001: PART5 | CN00002001001: PART6