Help need in using same table having Parent and child relationship

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  March 23, 2014 at 6:56 pm

  #305349

  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.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 23, 2014 at 9:54 pm

  #1699372

  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;

• Eirikur Eiriksson

  SSC Guru

  Points: 182918

  More actions

  March 23, 2014 at 11:38 pm

  #1699379

  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;

• thava

  SSCrazy

  Points: 2275

  More actions

  March 24, 2014 at 12:11 am

  #1699384

  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]

• Eirikur Eiriksson

  SSC Guru

  Points: 182918

  More actions

  March 24, 2014 at 1:37 am

  #1699402

  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;

• born2achieve

  Hall of Fame

  Points: 3182

  More actions

  March 24, 2014 at 5:36 am

  #1699478

  Guys,

  Thank you so much for the wonderful replies and really helped me to understand the scenario.