CODEWITHSUNDEEP
sql-serverhierarchy
Snowy
Snowy

Reputation: 6122

SQL Server Print Hierarchy of Tables?

I am trying to make a textual printout of a hierarchy represented in multiple tables. The tables look like this:

create table #par1 ( par1_id int primary key identity, par1_data varchar(8) )
create table #par2 ( par2_id int primary key identity, par1_id int , par2_data varchar(8) )
create table #par3 ( par3_id int primary key identity , par2_id int ,  par3_data varchar(8) )
create table #par4 ( par4_id int primary key identity , par3_id int ,  par4_data varchar(8) )
create table #par5 ( par5_id int primary key identity, par4_id int , par5_data varchar(8) )

insert into #par1 values ( 'a' )
insert into #par1 values ( 'b' )
insert into #par1 values ( 'c' )
insert into #par1 values ( 'c' )

insert into #par2 values ( 1 , 'aa' )
insert into #par2 values ( 2 , 'bb' )
insert into #par2 values ( 3, 'cc' )
insert into #par2 values ( 4, 'cc' )

insert into #par3 values ( 1 , 'aaa' )
insert into #par3 values ( 2 , 'bbb' )
insert into #par3 values ( 3 , 'ccc' )
insert into #par3 values ( 4 , 'ddd' )

insert into #par4 values ( 1 , 'aaaa' )
insert into #par4 values ( 2 , 'bbbb' )
insert into #par4 values ( 3 , 'cccc' )
insert into #par4 values ( 4 , 'dddd' )

insert into #par5 values ( 1 , 'wwwww' )
insert into #par5 values ( 1 , 'xxxxx' )
insert into #par5 values ( 1 , 'yyyyy' )
insert into #par5 values ( 1 , 'zzzzz' )

I monkeyed around with the code at http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm which creates the right format, but I can't make the break from that single-table structure to mine with multiple tables. What does the TSQL look like to make this kind of result:

----a
--------aa
------------aaa
----------------aaaa
--------------------wwwww
--------------------xxxxx
--------------------yyyyy
--------------------zzzzz
----b
--------bb
------------bbb
...etc

Thanks.

Upvotes: 0

Views: 401

Answers (1)

Matt Whitfield
Matt Whitfield

Reputation: 6574

It looks like middle tier code in the database.

But the T-SQL, which appends straight on to your script, is this:

CREATE TABLE #allPar (id int IDENTITY (1,1), originalID int, originalTable int, parentID int NULL, data varchar(8))

INSERT INTO [#allPar] ([originalID], [originalTable], [data])
SELECT [par1_id], 1, [par1_data] FROM #par1;

INSERT INTO [#allPar] ([originalID], [originalTable], [parentID], [data])
SELECT [par2_id], 2, [id], [par2_data] FROM #par2 INNER JOIN #allPar ON [#allPar].[originalID] = [#par2].[par1_id] AND [#allPar].[originalTable] = 1;

INSERT INTO [#allPar] ([originalID], [originalTable], [parentID], [data])
SELECT [par3_id], 3, [id], [par3_data] FROM #par3 INNER JOIN #allPar ON [#allPar].[originalID] = [#par3].[par2_id] AND [#allPar].[originalTable] = 2;

INSERT INTO [#allPar] ([originalID], [originalTable], [parentID], [data])
SELECT [par4_id], 4, [id], [par4_data] FROM #par4 INNER JOIN #allPar ON [#allPar].[originalID] = [#par4].[par3_id] AND [#allPar].[originalTable] = 3;

INSERT INTO [#allPar] ([originalID], [originalTable], [parentID], [data])
SELECT [par5_id], 5, [id], [par5_data] FROM #par5 INNER JOIN #allPar ON [#allPar].[originalID] = [#par5].[par4_id] AND [#allPar].[originalTable] = 4;

DECLARE @text [varchar](MAX);
SET @text = '';
WITH Recursed
AS
(
-- Anchor member definition
    SELECT [id], [originalID], [originalTable], [parentID], [data], 0 AS [Level], [originalID] AS rootID
    FROM #allPar [ap] WHERE [ap].[parentID] IS NULL
    UNION ALL
-- Recursive member definition
    SELECT [ap].[id], [ap].[originalID], [ap].[originalTable], [ap].[parentID], [ap].[data], [apParent].[Level] + 1, [rootID]
    FROM #allPar [ap] INNER JOIN Recursed [apParent]
        ON ap.[parentID] = [apParent].[id]
)
SELECT @text = @text + REPLICATE('-', [Level] * 4) + [data] + CHAR(13) + CHAR(10) FROM [Recursed] ORDER BY [rootID], [Level], [originalID]
PRINT @text

Upvotes: 1

Related Questions