Parent and child table structure

• Siten0308

  SSCarpal Tunnel

  Points: 4404

  More actions

  January 20, 2016 at 1:59 pm

  #303901

  Hello,

  not sure if anyone encountered this before, but I have a custom table made for me (sadly they didnt ask for my input), the structure is like this

  everything is in a row however they want the data arranged as

  Column ID Parent ID

  parent 123 null

  child 444 123

  child 555 123

  child 777 123

  parent 410 null

  child 555 410

  parent 987 null

  child 777 987

  so pretty much everything is in a row and need to be arranged and group by parent to child, the good

  thing is, as show above, the child ID will have the parent ID, and they need to be grouped together, doesn't matter about how the children are arranged, just matters that the results get grouped together by parent, any ideas?

  thanks in advanced

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 20, 2016 at 2:02 pm

  #1853295

  Siten0308 (1/20/2016)

  ---

  Hello,

  not sure if anyone encountered this before, but I have a custom table made for me (sadly they didnt ask for my input), the structure is like this

  everything is in a row however they want the data arranged as

  Column ID Parent ID

  parent 123 null

  child 444 123

  child 555 123

  child 777 123

  parent 410 null

  child 555 410

  parent 987 null

  child 777 987

  so pretty much everything is in a row and need to be arranged and group by parent to child, the good

  thing is, as show above, the child ID will have the parent ID, and they need to be grouped together, doesn't matter about how the children are arranged, just matters that the results get grouped together by parent, any ideas?

  thanks in advanced

  What do you mean everything is in a row?

  Can you post DDL and sample data as INSERT statements so we can try different solutions?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Siten0308

  SSCarpal Tunnel

  Points: 4404

  More actions

  January 20, 2016 at 2:17 pm

  #1853296

  sorry luis and everyone, here it is below:

  create table #InformationStuff

  (

  ID int,

  ParentID int,

  DescriptionInfo varchar(200)

  )

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,null,Parent1)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(2,null,Parent2)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(3,null,Parent3)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(12,1,child3)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,1,child5)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,1,child2)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,2,child3)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(5,2,child2)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,2,child1)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,3,child1)

  as you can see, the data in the temp table is not arranged by:

  Parent

  child

  child

  parent

  child etc.

  which is what they want, even though all the rows are not arranged like that already, so that is what I am trying to figure out, any ideas?

  thanks in advaned

• Manuel F.

  Valued Member

  Points: 61

  More actions

  January 20, 2016 at 2:48 pm

  #1853314

  Siten0308 (1/20/2016)

  ---

  sorry luis and everyone, here it is below:

  create table #InformationStuff

  (

  ID int,

  ParentID int,

  DescriptionInfo varchar(200)

  )

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,null,Parent1)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(2,null,Parent2)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(3,null,Parent3)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(12,1,child3)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,1,child5)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,1,child2)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,2,child3)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(5,2,child2)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,2,child1)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,3,child1)

  as you can see, the data in the temp table is not arranged by:

  Parent

  child

  child

  parent

  child etc.

  which is what they want, even though all the rows are not arranged like that already, so that is what I am trying to figure out, any ideas?

  thanks in advaned

  Try this Query:

  Select

  Case when parentId is null then 'parent' else 'child' end as type

  , isnull(parentId, id) as ord1

  , id

  , descriptionInfo

  From #InformationStuff

  Order by ord1, id

• Siten0308

  SSCarpal Tunnel

  Points: 4404

  More actions

  January 20, 2016 at 3:28 pm

  #1853327

  Cool thanks Manual, that almost has it, except for the Last parent, parent 3, the parent shows up after the child, so its:

  Child 3, 1

  Parent 3, 3

  need it

  Parent 3, 3

  Child 3, 1

  thanks again

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 20, 2016 at 4:52 pm

  #1853334

  I forgot to come back to mention that problem that might occur. It's easy to fix it. Remember that this is only for displaying purposes as there's no real order inside a table.

  SELECT *

  FROM #InformationStuff

  ORDER BY ISNULL(ParentID, ID), --Get groups in order

  ParentID, --Ensure that the parent is the first (NULLS always come first)

  ID; --Order the children

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Siten0308

  SSCarpal Tunnel

  Points: 4404

  More actions

  January 24, 2016 at 1:00 am

  #1854055

  Got ya thank you Luis and sorry for the late reply,

  so here is another question then, not sure if I should open another forum, but its related to the same, except now we have the end which is total, see below as an example:

  create table #InformationStuff

  (

  ID int,

  ParentID int,

  DescriptionInfo varchar(200),

  Total decimal(8,2)

  )

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(0,null,'GrandParent1',0.00)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,0,'Parent1',0.00)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(2,0,'Parent2',0.00)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(3,0,'Parent3',0.00)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(12,1,'child3',25.30)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,1,'child5'.10.15)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,1,'child2',5.25)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,2,'child3',8.75)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(5,2,'child2',2.10)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,2,'child1',3.25)

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,3,'child1',7.75)

  as you can see, so we have GrandParent - Multiple parents, - multiple children, but trying to figure out if I can use recursive to still accomplish? so totals from child, or sum all the children to parent, then all parent sum it all to grand parent for grand total 🙁

• Aditya Daruka

  Old Hand

  Points: 339

  More actions

  January 24, 2016 at 6:22 pm

  #1854080

  Something to play with :-

  ;WITH dataCTE AS

  (

  SELECT i.*

  , Sequence = CAST(i.ID AS VARCHAR(MAX))

  FROM #InformationStuff i

  WHERE i.ParentID IS NULL -- Top Level

  UNION ALL

  SELECT i.*

  , Sequence = d.Sequence + ', ' + CAST(i.ID AS VARCHAR(MAX))

  FROM #InformationStuff i

  INNER JOIN dataCTE d

  ON i.ParentID = d.ID

  )

  SELECT *

  FROM dataCTE

  ORDER BY Sequence ;

  --===================

  -- Temp Table

  --===================

  IF OBJECT_ID('tempdb..#InformationStuff') IS NOT NULL

  DROP TABLE #InformationStuff ;

  GO

  create table #InformationStuff

  (

  ID int,

  ParentID int,

  DescriptionInfo varchar(200)

  )

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,null,'Parent1')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(2,null,'Parent2')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(3,null,'Parent3')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(12,1,'child3')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,1,'child5')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,1,'child2')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(6,2,'child3')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(5,2,'child2')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(4,2,'child1')

  insert into #InformationStuff(ID,ParentID,DescriptionInfo)

  values(1,3,'child1')