June 24, 2016 at 4:00 am
idname parent_id
22Basic Info 76
27Ingredients 77
28Transparency 77
29Animal Testing 78
30Packaging 78
31Water Quality 78
76Target Sustainable Product Standard NULL
77Ingredients & Transparency NULL
78Minimal 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
idname parent_id
76Target Sustainable Product Standard NULL
22Basic Info 76
77Ingredients & Transparency NULL
27Ingredients 77
28Transparency 77
78Minimal Environmental Impact NULL
29Animal Testing 78
30Packaging 78
31Water 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
76Target Sustainable Product StandardNULL
77Ingredients & Transparency NULL
78Minimal Environmental Impact NULL
3455testGroup NULL
22Basic Info 76
27Ingredients 77
28Transparency 77
29Animal Testing 78
30Packaging 78
31Water Quality 78
3456test subGroup 3455
Is there any way to show parent and thens it corresponding childrens. like below
76Target Sustainable Product Standard NULL
22Basic Info76
77Ingredients & TransparencyNULL
27Ingredients77
28Transparency77
78Minimal Environmental ImpactNULL
29Animal Testing78
30Packaging78
31Water Quality78
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://www.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://www.sqlservercentral.com/articles/T-SQL/94570/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply