有个MSSQL表结构如下：
org_dept:部门(pk_dept：部门ID, name：部门名称, pk_fatherorg:上级部门ID)
根部门的上级部门ID是’~’
有两个方案：
方案一：用嵌套表方式生成视图；方案二：用函数实现传入ID传出各级部门，然后再展开
方案一代码：
alter view v_dept_flat
as
WITH Reordered_Dept AS (
SELECT
pk_dept,
name,
pk_fatherorg,
CAST(name AS VARCHAR(8000)) AS name_1,
CAST('' AS VARCHAR(8000)) AS name_2,
CAST('' AS VARCHAR(8000)) AS name_3,
CAST('' AS VARCHAR(8000)) AS name_4,
CAST('' AS VARCHAR(8000)) AS name_5,
CAST('' AS VARCHAR(8000)) AS name_6,
CAST('' AS VARCHAR(8000)) AS name_7,
CAST('' AS VARCHAR(8000)) AS name_8,
CAST('' AS VARCHAR(8000)) AS name_9,
CAST('' AS VARCHAR(8000)) AS name_10,
CAST(pk_dept AS VARCHAR(20)) AS id_1,
CAST('' AS VARCHAR(20)) AS id_2,
CAST('' AS VARCHAR(20)) AS id_3,
CAST('' AS VARCHAR(20)) AS id_4,
CAST('' AS VARCHAR(20)) AS id_5,
CAST('' AS VARCHAR(20)) AS id_6,
CAST('' AS VARCHAR(20)) AS id_7,
CAST('' AS VARCHAR(20)) AS id_8,
CAST('' AS VARCHAR(20)) AS id_9,
CAST('' AS VARCHAR(20)) AS id_10,
1 AS level
FROM org_dept
WHERE pk_fatherorg = '~'
and enablestate = 2 and hrcanceled = 'N'
UNION ALL
SELECT
d.pk_dept,
d.name,
d.pk_fatherorg,
r.name_1,
CASE WHEN r.level = 1 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_2 END,
CASE WHEN r.level = 2 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_3 END,
CASE WHEN r.level = 3 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_4 END,
CASE WHEN r.level = 4 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_5 END,
CASE WHEN r.level = 5 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_6 END,
CASE WHEN r.level = 6 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_7 END,
CASE WHEN r.level = 7 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_8 END,
CASE WHEN r.level = 8 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_9 END,
CASE WHEN r.level = 9 THEN CAST(d.name as VARCHAR(8000)) ELSE r.name_10 END,
r.id_1,
CASE WHEN r.level = 1 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_2 END,
CASE WHEN r.level = 2 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_3 END,
CASE WHEN r.level = 3 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_4 END,
CASE WHEN r.level = 4 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_5 END,
CASE WHEN r.level = 5 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_6 END,
CASE WHEN r.level = 6 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_7 END,
CASE WHEN r.level = 7 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_8 END,
CASE WHEN r.level = 8 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_9 END,
CASE WHEN r.level = 9 THEN CAST(d.pk_dept as VARCHAR(20)) ELSE r.id_10 END,
r.level + 1
FROM org_dept d
INNER JOIN Reordered_Dept r ON d.pk_fatherorg = r.pk_dept
where enablestate = 2 and hrcanceled = 'N'
)
SELECT * FROM Reordered_Dept方案二代码：
CREATE function [dbo].[fn_get_full_dept_info](@dept nvarchar(20))
RETURNS nvarchar(MAX) as
begin
-- declare @dept nvarchar(20)
declare @pk nvarchar(20)
declare @name nvarchar(200)
declare @id int
declare @row int
declare @pid nvarchar(20)
declare @result nvarchar(MAX)
DECLARE @array TABLE (
id INT primary key identity(1,1)
,pk nvarchar(20)
,name nvarchar(200)
);
-- set @dept = '1001A11000000000201G'
set @row = 1
select @pk = pk_dept, @name = name, @pid = pk_fatherorg from org_dept where pk_dept = @dept
insert into @array(pk,name) values(@pk, @name)
while @pid <> '~' begin
select @pk = pk_dept, @name = name, @pid = pk_fatherorg from org_dept where pk_dept = @pid
insert into @array(pk,name) values(@pk, @name)
set @row = @row + 1
end
set @result = ( select 1+@row - id as id,pk,name from @array order by id for json auto)
return @result
end
函数的使用方法如下，由于函数返回的信息比较多，使用了JSON格式，在使用结果的时候，可以按需用JSON函数解析
select pk_dept,code,name
, JSON_VALUE(JSON_QUERY(dbo.fn_get_full_dept_info(pk_dept), CONCAT('$[', 0, ']')), '$.name') AS name
, JSON_VALUE(JSON_QUERY(dbo.fn_get_full_dept_info(pk_dept), CONCAT('$[', 1, ']')), '$.name') AS name
, JSON_VALUE(JSON_QUERY(dbo.fn_get_full_dept_info(pk_dept), CONCAT('$[', 2, ']')), '$.name') AS name
, JSON_VALUE(JSON_QUERY(dbo.fn_get_full_dept_info(pk_dept), CONCAT('$[', 3, ']')), '$.name') AS name
, JSON_VALUE(JSON_QUERY(dbo.fn_get_full_dept_info(pk_dept), CONCAT('$[', 4, ']')), '$.name') AS name
from org_dept