Help need in using same table having Parent and child relationship
-
Hi,
Below is my sample data of my table named "Groups"
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
select * from groups;
Expected result:
with ExpectedResult as (
select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all
select '' as GroupName,'peoplesoft' as SubGroup union all
select '' as GroupName,'mysql' as SubGroup union all
select 'Microsoft' as GroupName,'Nokia' as SubGroup union all
select '' as GroupName,'EShop' as SubGroup union all
select 'IBM' as GroupName,'Meiosys' as SubGroup union all
select '' as GroupName,'UrbanCode' as SubGroup )
select * from ExpectedResult;
Please show me some sample query to how to achieve this parent-child has the same table.
thanks.
-
May not be the answer you are looking for but hopefully it points you in the right direction:
with Groups as (
select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup ),
rCTE as (
select
GroupId, GroupName, IdParentGroup, srtkey = cast(GroupName as varchar(max))
from
Groups
where
IdParentGroup = 0
union all
select
g.GroupId, g.GroupName, g.IdParentGroup, srtkey = srtkey + cast(g.GroupName as varchar(max))
from
Groups g
inner join rCTE r
on (g.IdParentGroup = r.GroupId)
)
select rc.GroupName, rc.IdParentGroup from rCTE rc order by rc.srtkey;
-
Or in other words
with Groups as (select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
SELECT
GP.GroupName
,GCH.GroupName
FROM Groups GCH
INNER JOIN Groups GP
ON GCH.IdParentGroup = GP.GroupId;
-
might be a little tweak of Lynm
WITH Groups AS
(
SELECT 1 AS GroupId, 'Oracle' AS GroupName, 0 AS IdParentGroup UNION ALL
SELECT 2 AS GroupId, 'Microsoft' AS GroupName, 0 AS IdParentGroup UNION ALL
SELECT 3 AS GroupId, 'IBM' AS GroupName, 0 AS IdParentGroup UNION ALL
SELECT 4 AS GroupId, 'SunMicrosystem' AS GroupName, 1 AS IdParentGroup UNION ALL
SELECT 5 AS GroupId, 'peoplesoft' AS GroupName, 1 AS IdParentGroup UNION ALL
SELECT 6 AS GroupId, 'mysql' AS GroupName, 1 AS IdParentGroup UNION ALL
SELECT 7 AS GroupId, 'Nokia' AS GroupName, 2 AS IdParentGroup UNION ALL
SELECT 8 AS GroupId, 'EShop' AS GroupName, 2 AS IdParentGroup UNION ALL
SELECT 9 AS GroupId, 'Meiosys' AS GroupName, 3 AS IdParentGroup UNION ALL
SELECT 10 AS GroupId, 'UrbanCode' AS GroupName, 3 AS IdParentGroup
),
rCTE AS (
SELECT CAST(0 AS BIGINT) as GroupNo, GroupId, GroupName as GroupName, GroupName as SubGroup, IdParentGroup
FROM Groups
WHERE IdParentGroup = 0
UNION ALL
SELECT ROW_NUMBER () OVER (ORDER BY g.GroupId) , g.GroupId, R.GroupName, g.GroupName AS SubGroup, g.IdParentGroup
FROM Groups g
INNER JOIN rCTE r
ON (g.IdParentGroup=r.GroupId)
)
SELECT case when Groupno=1 then GroupName ELSE '' END AS GroupName,SubGroup
FROM rCTE rc
WHERE groupno>0
ORDER BY
GroupId,groupno;
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Same query as before with the addition of a conditional statement to format the output.
with Groups as (select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all
select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all
select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all
select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all
select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all
select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all
select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all
select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all
select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all
select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup )
SELECT
CASE
WHEN ROW_NUMBER() OVER
(PARTITION BY GP.GroupName
ORDER BY GP.GroupName,GCH.GroupName) = 1 THEN GP.GroupName
ELSE ''
END AS ParentGroup
,GCH.GroupName
FROM Groups GCH
INNER JOIN Groups GP
ON GCH.IdParentGroup = GP.GroupId;
-
Guys,
Thank you so much for the wonderful replies and really helped me to understand the scenario.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply