June 24, 2016 at 4:00 am
id name parent_id
22 Basic Info 76
27 Ingredients 77
28 Transparency 77
29 Animal Testing 78
30 Packaging 78
31 Water Quality 78
76 Target Sustainable Product Standard NULL
77 Ingredients & Transparency NULL
78 Minimal Environmental Impact NULL
In the above table id=76,77,88 are parents records.For these parent records parent_id is null
Remaining ids are child ids.
for example id= 22 is child of id= 76.
Now i want to display the records in parent child order.
for example
id name parent_id
76 Target Sustainable Product Standard NULL
22 Basic Info 76
77 Ingredients & Transparency NULL
27 Ingredients 77
28 Transparency 77
78 Minimal Environmental Impact NULL
29 Animal Testing 78
30 Packaging 78
31 Water Quality 78
June 24, 2016 at 4:04 am
use recursive CTE (Common Table Expressions) concept to build the query.
June 24, 2016 at 4:10 am
;with cteSampleData
as
(
select id,name,parentid
from sampledata
where parentid is null
union all
select id, name parentid
from sampledata s2
inner join ctesampledata s1 on s1.id = s2.parentid
)
select id, name, parentid
from ctesampledata
option (maxrecursion 0)
order by parentid
June 24, 2016 at 6:21 am
;with cteSampleData
as
(
select id,name,parent_id
from f_groups
where parent_id is null and c_id=83
union all
select s2.id,s2.name,s2.parent_id
from f_groups s2
inner join cteSampleData s1 on s1.id = s2.parent_id and c_id=83
)
select ts.id,ts.name,ts.parent_id
from cteSampleData ts order by parent_id
option (maxrecursion 0)
I executed the above query.it is working fine. But It showing all the parents first and then childrens like below.
id name Parent_id
76 Target Sustainable Product Standard NULL
77 Ingredients & Transparency NULL
78 Minimal Environmental Impact NULL
3455 testGroup NULL
22 Basic Info 76
27 Ingredients 77
28 Transparency 77
29 Animal Testing 78
30 Packaging 78
31 Water Quality 78
3456 test subGroup 3455
Is there any way to show parent and thens it corresponding childrens. like below
76 Target Sustainable Product Standard NULL
22 Basic Info 76
77 Ingredients & Transparency NULL
27 Ingredients 77
28 Transparency 77
78 Minimal Environmental Impact NULL
29 Animal Testing 78
30 Packaging 78
31 Water Quality 78
June 24, 2016 at 7:38 am
do a web search for itzik ben-gan hierarchy
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 24, 2016 at 4:17 pm
If the hierarchy is large, consider the use of Nested Sets while still maintaining the Adjacency List. See the following articles for how to do such a thing in an unconventional fashion using a Tally Table to do the conversion of a million row hierarchy in about 54 seconds or faster.
http://qa.sqlservercentral.com/articles/Hierarchy/94040/
If you have certain types of calculations that need to be done, you can also benefit from the same method to do them ALL at once, creating a kind of hierarchical datamart with most of the answers available as simple index-able lookups.
http://qa.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply