用存储过程将数据取出并排列成树状，然后直接打印即可
这是一个打印树形类别的存储过程，同样可以打印BOm
--这代码可能不适合，但可以分析出存储过程处理树形
结构数据表的思路
参数:
@TypeTable nvarchar(50) 表名称
@FatherKey nvarchar(30), 父Key
@ChildKey nvarchar(30), 子Key
@RootKey nvarchar(50) 起始节点
CREATE PROCEDURE uspGetTypeTree
@TypeTable nvarchar(50),
@FatherKey nvarchar(30),
@ChildKey nvarchar(30),
@RootKey nvarchar(50)
AS
DECLARE
@CCount INT,
@FTypeId nvarchar(20),
@CTypeId nvarchar(20),
@NodeKey nvarchar(200),
@cmd nvarchar(200),
@SubNodeKey int,
@Shape1 nvarchar(1),
@Shape2 nvarchar(1),
@Shape3 nvarchar(1),
@Shape4 nvarchar(1),
@Shape5 nvarchar(1)
SELECT @Shape1='│'
SELECT @Shape2='─'
SELECT @Shape3='├'
SELECT @Shape4='└'
SELECT @Shape5=' '
SET NOCOUNT ON
CREATE TABLE #Model(FTypeId nvarchar(20), Nodekey nvarchar(200) )
CREATE TABLE #Part (CTypeId nvarchar(20), Nodekey nvarchar(200) )
CREATE TABLE #Last (TypeId nvarchar(20), Nodekey nvarchar(200) )
CREATE TABLE #CCC (CCC int)
Insert Into #Model Values (@RootKey,'0000')
Insert Into #Last Values (@RootKey,'0000')
WHILE Exists(SELECT * FROM #Model)
begin
--取某节点的下级结构,存入#Part
DELETE FROM #Part
DECLARE curModel CURSOR FOR Select * From #Model
Open curModel
FETCH curModel INTO @FTypeId,@NodeKey
WHILE @@FETCH_STATUS =0
begin
SELECT @cmd='Select '+@ChildKey+','''+@NodeKey+''' From '+@TypeTable+' WHERE '+@FatherKey+'='''+@FTypeId+''''
INSERT INTO #Part Execute(@cmd)
FETCH curModel INTO @FTypeId,@NodeKey
END
CLOSE curModel
DEALLOCATE curModel
DELETE FROM #Model
Select @SubNodeKey=0
--判断#Part 中的节点有无下级节点
DECLARE curPart CURSOR FOR Select * From #Part
Open curPart
FETCH curPart INTO @CTypeId,@NodeKey
WHILE @@FETCH_STATUS =0
begin
SELECT @SubNodeKey=@SubNodeKey+1
select @cmd='SELECT Count(*) As CCC FROM '+@TypeTable+' WHERE '+@FatherKey+'='''+@CTypeId+''''
Delete From #CCC
insert into #ccc Execute(@cmd)
Select @cCount=ccc from #ccc
IF @ccOUNT=0
begin
--无下级节点 存入#LAst
INSERT INTO #Last VALUES (@CTypeId,@NodeKey+Right('000'+ltrim(rtrim(str(@subNodeKey))),4))
END
else
begin
--有下级节点,将该节点转入#Model,#Last
INSERT INTO #Model VALUES (@CTypeId,@NodeKey+Right('000'+ltrim(rtrim(str(@subNodeKey))),4))
INSERT INTO #Last VALUES (@CTypeId,@NodeKey+Right('000'+ltrim(rtrim(str(@subNodeKey))),4))
END
FETCH curPart INTO @CTypeId,@NodeKey
END
CLOSE curPart
DEALLOCATE curPart
END
--画一棵树
SELECT replicate(@Shape5,300) as Header, * Into #Last2 From #Last Order By NodeKey
DECLARE curLast CURSOR FOR Select NodeKey From #Last2 ORDER BY Nodekey DESC
OPEN curLast
FETCH curLast Into @NodeKey
WHILE @@fetch_status=0
begin
Update #Last2 Set Header=SUBSTRING(Header,1,len(@NodeKey)-1)+@Shape1+replicate(@shape5,200)
WHERE NodeKey<@NodeKey AND NodeKey>SUBSTRing(@Nodekey,1,Len(@NodeKey)-4)
Update #Last2 Set Header=SUBSTRING(Header,1,len(@NodeKey)-1)+(Case SUBSTRING(Header,len(@NodeKey),1)
when @Shape1 then
@shape3
WHEN @Shape5 then
@shape4
else
@shape4
end )+Replicate(@shape2,4)
WHERE NodeKey=@NodeKey
FETCH curLast Into @NodeKey
END
CLOSE curLast
DEALLOCATE curLast
Update #last2 set Header=replicate(@shape5,6) where Nodekey='0000'
SELECT rtrim(substring(header,8,255))+ltrim(rtrim(typeid)) as tree,*,0.000 aS tOTmONEY,0.000 As TotQty,0.000 As AvPrice FROM #Last2
