有限层树形数据转换成无限层树形数据的示例存储过程(MS SQL 2K)

                --有限层树形数据转换成无限层树形数据的示例存储过程(MS SQL 2K)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_Tree_Convert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_Tree_Convert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

 

CREATE   PROCEDURE P_Tree_Convert AS

--有限层树形数据转换成无限层树形数据的示例存储过程

--结果数据.
CREATE TABLE [dbo].[#TREE_INFO] (
 [NODEID] [varchar](10) NULL , --树形数据节点代码
 [PARENTID] [varchar] (10) NULL ,--树形数据节点的父节点代码
 [NODENAME] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,--节点名称
 [ADDRESS] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,--节点的其它信息
 [ICON] [int] NULL --节点的图标编号
) ON [PRIMARY]

--获取有限层树形数据节点数据
SELECT * into #
FROM (SELECT DPCODE1 AS node, DPNAME1 AS nodename
        FROM ora_CUSTDEPT
        WHERE (DPCODE2 = '00') AND (DPCODE3 = '000') AND (DPCODE4 = '000')
        UNION ALL
        SELECT DPCODE1 + DPCODE2 AS node, DPNAME2 AS nodename
        FROM ora_CUSTDEPT
        WHERE (DPCODE3 = '000') AND (DPCODE4 = '000') AND (DPCODE2 <> '00')
        UNION ALL
        SELECT DPCODE1 + DPCODE2 + DPCODE3 AS node,
              DPNAME3 AS nodename
        FROM ora_CUSTDEPT
        WHERE (DPCODE3 <> '000') AND (DPCODE4 = '000') AND (DPCODE2 <> '00')
        UNION ALL
        SELECT DPCODE1 + DPCODE2 + DPCODE3 + DPCODE4 AS node,
              DPNAME4 AS nodename
        FROM ora_CUSTDEPT
        WHERE (DPCODE3 <> '000') AND (DPCODE4 <> '000') AND (DPCODE2 <> '00'))
      tree_node
ORDER BY node

insert into  #TREE_INFO(NODEID, PARENTID, NODENAME)
--从有限层树形数据节点数据获取其父节点数据,后插入结果表
SELECT node, CASE WHEN len(ltrim(rtrim(node))) = 2 THEN '0' WHEN len(node)
      = 4 THEN substring(ltrim(rtrim(node)), 1, 2) WHEN len(node)
      = 7 THEN substring(ltrim(rtrim(node)), 1, 4) WHEN len(node)
      = 10 THEN substring(ltrim(rtrim(node)), 1, 7) END AS parentid,nodename
FROM #

select * from #TREE_INFO  --结果查看
 
drop table #
drop table #TREE_INFO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO