vijay11
asked on
Help to write the sql
Hi i have a table like below
1 abc 2 def 3 xcg 4 bhj 1 abc
6 hju 8 hjk 9 huk 13 bhk 18 bhj
I want output as
1 abc 4 bhj
4 bhj 3 xcg
3 Xcg 2 def
2 Def 1 abc
Source table represents the parent child relation ship.in source table 1st column is the last child and 2nd column is its description.1 is the child of 4.4 is the child of 3 etc
Can u pls help to write this logic
1 abc 2 def 3 xcg 4 bhj 1 abc
6 hju 8 hjk 9 huk 13 bhk 18 bhj
I want output as
1 abc 4 bhj
4 bhj 3 xcg
3 Xcg 2 def
2 Def 1 abc
Source table represents the parent child relation ship.in source table 1st column is the last child and 2nd column is its description.1 is the child of 4.4 is the child of 3 etc
Can u pls help to write this logic
Could you please provide the table name and field names?
Is this correct?
record a ==>> 1, abc, 2, def, 3, xcg, 4, bhj, 1, abc
record b ==>> 6, hju, 8, hjk, 9, huk, 13, bhk, 6, hju <<== 6, hju is now repeated
Is this correct?
record a ==>> 1, abc, 2, def, 3, xcg, 4, bhj, 1, abc
record b ==>> 6, hju, 8, hjk, 9, huk, 13, bhk, 6, hju <<== 6, hju is now repeated
It would be easier with the real table & field names, but I was able to produce this result:
| CHILD | CHILDDESC | PARENT | PARENTDESC |
|-------|-----------|--------|------------|
| 1 | abc | 4 | bhj |
| 4 | bhj | 3 | xcg |
| 3 | xcg | 2 | def |
| 2 | def | 1 | abc |
| 6 | hju | 13 | bhk |
| 13 | bhk | 9 | huk |
| 9 | huk | 8 | hjk |
| 8 | hjk | 6 | hju |
Using the following query:
SELECT
Child, ChildDesc, Parent, ParentDesc
FROM Table1
CROSS APPLY (
VALUES
(he, de, hd, dd)
,(hd, dd, hc, dc)
,(hc, dc, hb, db)
,(hb, db, ha, da)
) AS ca1 (Child, ChildDesc, Parent, ParentDesc)
;
-- http://sqlfiddle.com/#!3/b978b/3
This technique uses CROSS APPLY and then VALUES. Within the values you may then arrange the rows and column contents to suit the wanted output layout.
ASKER
Here's the source table
insert source(child,childdescr,lv l1,lvl1_de sc,lvl2,lv l2_desc,lv l3,lvl3_de sc,lvl4,lv l4_desc,lv l5,lvl6_de sc,lvl7,lv l7_desc)
select 1, abc, 2, def, 3, xcg, 4 ,bhj ,1 ,abc,null,null
select 6 ,hju , 8,hjk, 9, huk, 13, bhk, 18, bhj,6,hju
I want out put as
1 abc 4 bhj
4 bhj 3 xcg
3 Xcg 2 def
2 Def 1 abc
insert source(child,childdescr,lv
select 1, abc, 2, def, 3, xcg, 4 ,bhj ,1 ,abc,null,null
select 6 ,hju , 8,hjk, 9, huk, 13, bhk, 18, bhj,6,hju
I want out put as
1 abc 4 bhj
4 bhj 3 xcg
3 Xcg 2 def
2 Def 1 abc
ASKER
Can you pls explain why are you using
VALUES
(he, de, hd, dd)
,(hd, dd, hc, dc)
,(hc, dc, hb, db)
,(hb, db, ha, da)
I have more than 50 columns .
Thanks
VALUES
(he, de, hd, dd)
,(hd, dd, hc, dc)
,(hc, dc, hb, db)
,(hb, db, ha, da)
I have more than 50 columns .
Thanks
ASKER
Some have more than 10 parent child relation and some don't have any pls let me know
If you have more than 50 columns than I would suggest providing that information in the question.
Ha Hb Hc Hd He = H for hierarchy + a, b, c, d, e for the 5 columns you indicated were invloved
Da... = D for Description
I'll wait for full facts of the table now.
Ha Hb Hc Hd He = H for hierarchy + a, b, c, d, e for the 5 columns you indicated were invloved
Da... = D for Description
I'll wait for full facts of the table now.
Can you pls explain why are you usingI explained why I did this and it was in response to the details given in the question. I did not know you have more than 50 columns when providing that answer.
VALUES
(he, de, hd, dd)
,(hd, dd, hc, dc)
,(hc, dc, hb, db)
,(hb, db, ha, da)
I have more than 50 columns .
Thanks
Other suggestions
provide column heading for the wanted output and list out the expected results in full from the second record of sample data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks.
Please do remember for your next question that proving the real table and field names (all of them) will help us answer and help you too as you won't need to "translate" an answer to suit your names etc.
Cheers, Paul
Please do remember for your next question that proving the real table and field names (all of them) will help us answer and help you too as you won't need to "translate" an answer to suit your names etc.
Cheers, Paul
ASKER