x

Need hierarchical result as shown below

suppose i have the following table

alt text

from that data i want the following result

alt text

there can be n number of subs

table.png (5.3 kB)
result.png (4.2 kB)
more ▼

asked May 17 at 12:53 PM in Default

avatar image

mwaqasaziz
1 2

@JohnM provided a great answer for an "unlimited number of subs" below but will only work for 3 levels deep. How many levels do you actually have? And, could that number change?

May 17 at 08:41 PM Jeff Moden

thanks to all who responded, Jeff Moden yes you r right, the problem that i encountered is that there can be undefined number of subs,a user he/she should be allowed to create any number of subs.

May 18 at 04:39 AM mwaqasaziz

Ok. The question now becomes, do you actually need the result as separate columns (as you've posted it) or can it be a single column with a, say, comma separated list of the items. If the former, do you have a pattern of column names that you need to follow (my suggestion would be Level01, Level02, Level03, etc).

5 days ago Jeff Moden
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

This is what I came up with:

 CREATE TABLE #stuff (id INT, name VARCHAR(50), parent_id INT)
 
 INSERT INTO #stuff (id, name, parent_id)
     SELECT 1, 'Food', NULL UNION
     SELECT 2, 'Beverage', NULL UNION
     SELECT 3, 'Soft Drink', 2 UNION
     SELECT 4, 'Alcohol', 2 UNION
     SELECT 5, 'Bakery', 1 UNION
     SELECT 6, 'Coke', 3 UNION
     SELECT 7, 'Pepsi', 3 UNION
     SELECT 8, 'Vodka', 4 UNION
     SELECT 9, 'Wine', 4 UNION
     SELECT 10, 'Bread', 5 UNION
     SELECT 11, 'Cake', 5
 
 SELECT c.name, b.name, a.name FROM #stuff a
     INNER JOIN #stuff b ON a.parent_id = b.id
     INNER JOIN #stuff c ON b.parent_ID = c.id

Note: There certainly could be better/more effective ways to do this.

more ▼

answered May 17 at 03:58 PM

avatar image

JohnM
13.7k 3 7 14

Cool method. +1

May 17 at 06:42 PM Tim

Many thanks, but i need the query for any number of subs

May 18 at 04:40 AM mwaqasaziz

@JohnM I would like to point out a small touch up which is needed:

Even for predetermined number of levels, such as 3 in this answer, the script will not work as written if the hierarchy is ragged. For example, delete the record with ID in (10, 11)(Bread and Cake), and the food record will be gone, even though there is a food/bakery combination which is still there, but bakery does not have any child records. Similarly, if you insert the row with Parent_Id is null then such record will not show up in the results either. The script will have to be slightly modified to replace all inner joins with right joins (to preserve the order of the tables as is, that is from last-level to first level records) and also adding a where clause: WHERE c.Parent_Id is null.

4 days ago Oleg

@JohnM I would like to point out a small touch up which is needed:

Even for predetermined number of levels, such as 3 in this answer, the script will not work as written if the hierarchy is ragged. For example, delete the record with ID in (10, 11)(Bread and Cake), and the food record will be gone, even though there is a food/bakery combination which is still there, but bakery does not have any child records. Similarly, if you insert the row with Parent_Id is null then such record will not show up in the results either. The script will have to be slightly modified to replace all inner joins with right joins (to preserve the order of the tables as is, that is from last-level to first level records) and also adding a where clause: WHERE c.Parent_Id is null.

4 days ago Oleg
(comments are locked)
10|1200 characters needed characters left

This answers borrows the logic from @JohnM answer, it just makes the query dynamic to accommodate the dynamic number of levels, and also considers that the hierarchy may be ragged. Before going any further, I need to point out that the nested sets design in place of the adjacent list may hugely benefit performance. Please read the excellent article by @Jeff Moden titled Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

The idea is that the recursive CTE is used just to figure the number of levels, that is all. Once this is determined, the dynamic script is generated, preserving the logic in @JohnM answer (just with order reversed so the left join is used instead of the right join). Once generated, the script can be executed. Please note that the sample data includes a row for one parent which does not have any child records, and also includes the row for Tiramisu cake, so that this is the only record on the last level. Other rows are the same as in the original question and @JohnM answer. Here is the script:

 -- mockup some data
 use tempdb;
 go
 
 create table dbo.t (Id int not null, [Name] varchar(20) not null, Parent_Id int null);
 insert into t values
 (0, 'Something else', null), -- this parent record has no child records
 (1, 'food', null),
 (2, 'beverage', null),
 (3, 'Soft Drink', 2),
 (4, 'Alcohol', 2),
 (5, 'Bakery', 1),
 (6, 'Coke', 3),
 (7, 'Pepsi', 3),
 (8, 'Vodka', 4),
 (9, 'Vine', 4),
 (10, 'Bread', 5),
 (11, 'Cake', 5),
 (12, 'Tiramisu', 11) -- the only record on that level;
 
 declare @maxLevel int;
 declare @columns varchar(max);
 declare @joins varchar(max);
 declare @sql nvarchar(max);
 
 -- determine the number of levels. Because the table is designed as adjacent
 -- list, the recursive CTE is used. It is not going to be too heavy though.
 ; with hierarchy as (
     select
         id, 0 TheLevel
         from dbo.t
         where Parent_Id is null
     union all
         select 
             t.id, h.TheLevel + 1
             from dbo.t inner join hierarchy h
                 on t.Parent_Id = h.id
 )
     select @maxLevel = max(TheLevel) from hierarchy;
 
 -- build the column list
 select @columns = 
     stuff(
     (
         select 
             ', ' + quotename(number) + '.[name] Level' + cast(number as varchar)
             from master.dbo.spt_values /* Tally table should be used instead */
             where number <= @maxLevel and [type] = 'P'
             for xml path('')
     ), 1, 2, '');
 
 -- build the list of the join conditions;
 select @joins = (
     select 
         'left join dbo.t ' + quotename(number) + ' on ' + quotename(number - 1) +
         '.id = ' + quotename(number) + '.Parent_Id' + char(10)
         from master.dbo.spt_values /* Tally table should be used instead */
         where number between 1 and @maxLevel and [type] = 'P'
         for xml path('')
     );
 
 select 
     @sql = N'select ' + @columns + '
     from dbo.t [0] ' + @joins + 'where [0].Parent_Id is null';
 
 -- uncomment with retults to text to see how the final statement looks
 -- select @sql
 
 exec sp_executesql @statement = @sql;
 go
 
 -- cleanup
 drop table dbo.t;
 go

This produces the following result:

 Level0          Level1          Level2          Level3
 --------------- --------------- --------------- ---------------
 Something else  NULL            NULL            NULL
 food            Bakery          Bread           NULL
 food            Bakery          Cake            Tiramisu
 beverage        Soft Drink      Coke            NULL
 beverage        Soft Drink      Pepsi           NULL
 beverage        Alcohol         Vodka           NULL
 beverage        Alcohol         Vine            NULL

Hope this helps.

Oleg

Sorry, forgot to mention that because the SQL Server in question is 2016, the string-agg should be used in place of for xml path('') for concatenating the strings. In this case for xml path('') is not going to hurt because the concatenations are small, but generally speaking it is a good idea to start using this now available method.

more ▼

answered May 21 at 05:40 AM

avatar image

Oleg
17.5k 3 7 28

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x8

asked: May 17 at 12:53 PM

Seen: 35 times

Last Updated: 4 days ago

Copyright 2016 Redgate Software. Privacy Policy