未分类

如何把一个父子结构的表拉成平面表

有个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