有限层树形数据转换成无限层树形数据的示例存储过程(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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值