| Author |
 Topic  |
|
|
manojn1707
Starting Member
4 Posts |
 Posted - 04/14/2012 : 11:19:34
|
Hi Experts,
I need your help in extracting parent child relationship from two columns. Below is the table structure.
Table Name : test1
No Name
E1 A1
E2 A2
E3 B1
Table Name: test2
Name Parent
A1 A2
A2 A3
A3 NULL
B1 NULL
Expected Result:
No Name Parent 1 Parent2
E1 A1 A2 A3
E2 A2 A3
E3 B1
Can you try to design query for extracting above result .
Regards,
Manoj N
|
|
|
ahmeds08
Constraint Violating Yak Guru
India
444 Posts |
Posted - 04/14/2012 : 17:30:32
|
| where is that you are storing PARENT1 and PARENT2?? |
 |
|
|
manojn1707
Starting Member
4 Posts |
Posted - 04/15/2012 : 00:04:55
|
Hi Ahmed,
Parent 1 , Parent 2 are the values derived from Table test2.
Table Name: test2
Name Parent
A1 A2
A2 A3
A3 NULL
B1 NULL
In the above table ,
1. A2 is the parent of A1,
2. A3 is the parent of A2.
3. B1 is not having any parent.
So in the final result, the parent of every child should be displayed in new columns.
Regards,
Manoj N.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
|
|
manojn1707
Starting Member
4 Posts |
Posted - 04/15/2012 : 14:23:19
|
Hi visakh16,
Thanks for sharing the link. I tried with the below query, but received "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
" error.
; WITH G3
AS
(
select * from test1 join test2 where test1.name = test2.name and
UNION ALL
SELECT * FROM test2 g4 , test2 G2 where G2.NAME = g4.PARENT
) SELECT * FROM G3
Regards,
Manoj N. |
|
|
|
manojn1707
Starting Member
4 Posts |
Posted - 04/15/2012 : 15:32:04
|
Hi Visakh,
Thanks for the link. I m able to analyse and design the for table test2 ...
With the below query i m able to retrieve parent child relationship..
; WITH G3
AS
( SELECT G2.NAME, G2.PARENT
FROM TEST2 G2 WHERE G2.NAME = 'A1'
UNION ALL
SELECT G2.NAME, G2.PARENT FROM TEST2 G2 INNER JOIN G3.PARENT = G2.NAME
) SELECT NAME FROM G3
Now i m getting result as ....
NAME
A1
A2
A3
Next jobs is to convert tranpose the values into multiple columns as below...
NAME PARENT1 PARENT2
A1 A2 A3
Regards,
Manoj. N. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
|
| |
Topic |
|
Parent Child Relationship
