Query help to generate relationship

Question

Query help to generate relationship

mxy

SSCarpal Tunnel

Points: 4036
More actions
July 18, 2015 at 11:06 am

#319109

Hello,
I am working on a query to generate parent child hierarchy from a table.
Table has below records.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
AccountID INT
,ParentID INT
,ChildID INT
)
---Populate tempTable
INSERT INTO #mytable
select 1 ,1 ,1
union all select 2 ,1 ,2
union all select 3 ,2 ,3
union all select 4 ,1 ,3
union all select 5 ,2 ,5
union all select 6 ,5 ,1
SELECT * FROM #mytable
--O/P
Ex : Account 5 has child 1
Account 2 has 3,5
Account 1 has childern 1,2,3
how to achieve this.l tried with temp tables it doesnt work. pls help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

mxy (7/18/2015)
Hello,
I am working on a query to generate parent child hierarchy from a table.
Table has below records.
Col1ID Col2Parent Col3Child
1 1 1
2 1 2
3 2 3
4 1 3
5 2 5
6 5 1
O/P should be for an account id 1 it has child 1,2,3 and 1 is child of 5
account Id 2 has child 3,5 and 2 is child of 1
how to achieve this.l tried with temp tables it doesnt work. pls help

Are you sure that's actually an accurate representation of the data? You have a child that is it's own parent and children that have more than one parent. Typically, parent/child tables (also known as Adjacency lists) have a unique constraint on the child column.

Also, for much better help, please see the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mxy SSCarpal Tunnel Points: 4036 More actions · Answer 2

thanks for your reply Jeff. Data is correct,

Ex: BOFA has multiple share holders and it can hold shares on its own account.

If BOFA has a child as bank A,B

Bank B can have shares in A as well

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

mxy (7/18/2015)
thanks for your reply Jeff. Data is correct,
Ex: BOFA has multiple share holders and it can hold shares on its own account.
If BOFA has a child as bank A,B
Bank B can have shares in A as well

~~Ok. The other thing is did you read the first link in my signature line below? That will get you some quicker help.~~

EDIT... never mind... I see that you changed your original post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sgmunson SSC Guru Points: 110640 More actions · Answer 4

mxy (7/18/2015)
thanks for your reply Jeff. Data is correct,
Ex: BOFA has multiple share holders and it can hold shares on its own account.
If BOFA has a child as bank A,B
Bank B can have shares in A as well

Then you don't quite have what is traditionally referred to as a heirarchy. You have a hybrid of a rather different sort, and traditional heirarchy navigation techniques aren't going to help, as such techniques would inherently gag on a parent being it's own child. Such techniques usually rely on that not being the case, lest their recursive nature become an infinite loop. You'll have to be very specific in defining what you expect the "allowed" relationships to be.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

Based on what you have and the output that you have shown, the following should give you what you're looking for...

SELECT

m1.ParentID AS Account_ID,

STUFF((

SELECT ', ' + CAST(m2.ChildID AS VARCHAR(8))

FROM #mytable m2

WHERE m1.ParentID = m2.ParentID

FOR XML PATH('')), 1, 2, '') AS ChildList

FROM

#mytable m1

GROUP BY

m1.ParentID

Here is the output...

Account_ID ChildList

----------- ------------

1 1, 2, 3

2 3, 5

5 1

HTH,

Jason