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
vijay11Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
You may extend the logic for over 50 fields, see below. Note you need to adjust the commas to suit.
    CREATE TABLE YourTable
    	([child] int, [childdescr] varchar(3), [lvl1] int, [lvl1_desc] varchar(3), [lvl2] int, [lvl2_desc] varchar(3), [lvl3] int, [lvl3_desc] varchar(3), [lvl4] int, [lvl4_desc] varchar(3), [lvl5] varchar(4), [lvl5_desc] varchar(4), [lvl6] varchar(4), [lvl6_desc] varchar(4))
    ;
    	
    INSERT INTO YourTable
    	([child], [childdescr], [lvl1], [lvl1_desc], [lvl2], [lvl2_desc], [lvl3], [lvl3_desc], [lvl4], [lvl4_desc], [lvl5], [lvl5_desc], [lvl6], [lvl6_desc])
    VALUES
    	(1, 'abc', 2, 'def', 3, 'xcg', 4, 'bhj', 1, 'abc', NULL, NULL, NULL, NULL),
    	(6, 'hju', 8, 'hjk', 9, 'huk', 13, 'bhk', 18, 'bhj', '6', 'hju', NULL, NULL)
    ;

**Query 1**:

    SELECT
           Col1, Col1_Desc, Col2, Col2_Desc
    FROM YourTable
    CROSS apply (
          VALUES
      /*
                  (lvl50, lvl50_desc, lvl49, lvl49_desc)
                 ,(lvl49, lvl49_desc, lvl48, lvl48_desc)
                 ,(lvl48, lvl48_desc, lvl47, lvl47_desc)
                 ,(lvl47, lvl47_desc, lvl46, lvl46_desc)
                 ,(lvl46, lvl46_desc, lvl45, lvl45_desc)
                 ,(lvl45, lvl45_desc, lvl44, lvl44_desc)
                 ,(lvl44, lvl44_desc, lvl43, lvl43_desc)
                 ,(lvl43, lvl43_desc, lvl42, lvl42_desc)
                 ,(lvl42, lvl42_desc, lvl41, lvl41_desc)
                 ,(lvl41, lvl41_desc, lvl40, lvl40_desc)
                 ,(lvl40, lvl40_desc, lvl39, lvl39_desc)
                 ,(lvl39, lvl39_desc, lvl38, lvl38_desc)
                 ,(lvl38, lvl38_desc, lvl37, lvl37_desc)
                 ,(lvl37, lvl37_desc, lvl36, lvl36_desc)
                 ,(lvl36, lvl36_desc, lvl35, lvl35_desc)
                 ,(lvl35, lvl35_desc, lvl34, lvl34_desc)
                 ,(lvl34, lvl34_desc, lvl33, lvl33_desc)
                 ,(lvl33, lvl33_desc, lvl32, lvl32_desc)
                 ,(lvl32, lvl32_desc, lvl31, lvl31_desc)
                 ,(lvl31, lvl31_desc, lvl30, lvl30_desc)
                 ,(lvl30, lvl30_desc, lvl29, lvl29_desc)
                 ,(lvl29, lvl29_desc, lvl28, lvl28_desc)
                 ,(lvl28, lvl28_desc, lvl27, lvl27_desc)
                 ,(lvl27, lvl27_desc, lvl26, lvl26_desc)
                 ,(lvl26, lvl26_desc, lvl25, lvl25_desc)
                 ,(lvl25, lvl25_desc, lvl24, lvl24_desc)
                 ,(lvl24, lvl24_desc, lvl23, lvl23_desc)
                 ,(lvl23, lvl23_desc, lvl22, lvl22_desc)
                 ,(lvl22, lvl22_desc, lvl21, lvl21_desc)
                 ,(lvl21, lvl21_desc, lvl20, lvl20_desc)
                 ,(lvl20, lvl20_desc, lvl19, lvl19_desc)
                 ,(lvl19, lvl19_desc, lvl18, lvl18_desc)
                 ,(lvl18, lvl18_desc, lvl17, lvl17_desc)
                 ,(lvl17, lvl17_desc, lvl16, lvl16_desc)
                 ,(lvl16, lvl16_desc, lvl15, lvl15_desc)
                 ,(lvl15, lvl15_desc, lvl14, lvl14_desc)
                 ,(lvl14, lvl14_desc, lvl13, lvl13_desc)
                 ,(lvl13, lvl13_desc, lvl12, lvl12_desc)
                 ,(lvl12, lvl12_desc, lvl11, lvl11_desc)
                 ,(lvl11, lvl11_desc, lvl10, lvl10_desc)
                 ,(lvl10, lvl10_desc, lvl9, lvl9_desc)
                 ,(lvl9, lvl9_desc, lvl8, lvl8_desc)
                 ,(lvl8, lvl8_desc, lvl7, lvl7_desc)
                 ,(lvl7, lvl7_desc, lvl6, lvl6_desc)
                 , */
                  (lvl6, lvl6_desc, lvl5, lvl5_desc)
                 ,(lvl5, lvl5_desc, lvl4, lvl4_desc)
                 ,(lvl4, lvl4_desc, lvl3, lvl3_desc)
                 ,(lvl3, lvl3_desc, lvl2, lvl2_desc)
                 ,(lvl2, lvl2_desc, lvl1, lvl1_desc)
                 ,(lvl1, lvl1_desc, child, childdescr)
                ) AS ca1 (Col1, Col1_Desc, Col2, Col2_Desc)
    WHERE col1 IS NOT NULL
    

**[Results][2]**:
    
    | COL1 | COL1_DESC | COL2 | COL2_DESC |
    |------|-----------|------|-----------|
    |    1 |       abc |    4 |       bhj |
    |    4 |       bhj |    3 |       xcg |
    |    3 |       xcg |    2 |       def |
    |    2 |       def |    1 |       abc |
    |    6 |       hju |   18 |       bhj |
    |   18 |       bhj |   13 |       bhk |
    |   13 |       bhk |    9 |       huk |
    |    9 |       huk |    8 |       hjk |
    |    8 |       hjk |    6 |       hju |



  [1]: http://sqlfiddle.com/#!3/6e0be/2

Open in new window

0
 
vijay11Author Commented:
Sorry 2nd row in source table last 2 columns should be 6 hju
0
 
PortletPaulfreelancerCommented:
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
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
PortletPaulfreelancerCommented:
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 |

Open in new window

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

Open in new window

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.
0
 
vijay11Author Commented:
Here's the source table

insert source(child,childdescr,lvl1,lvl1_desc,lvl2,lvl2_desc,lvl3,lvl3_desc,lvl4,lvl4_desc,lvl5,lvl6_desc,lvl7,lvl7_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
0
 
vijay11Author Commented:
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
0
 
vijay11Author Commented:
Some have more than 10 parent child relation and some don't have any pls let me know
0
 
PortletPaulfreelancerCommented:
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.
0
 
PortletPaulfreelancerCommented:
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
I 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.

Other suggestions
provide column heading for the wanted output and list out the expected results in full from the second record of sample data.
0
 
PortletPaulfreelancerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.