Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help to write the sql

Posted on 2013-11-19
11
Medium Priority
?
304 Views
Last Modified: 2013-11-19
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
0
Comment
Question by:vijay11
  • 6
  • 4
10 Comments
 

Author Comment

by:vijay11
ID: 39660317
Sorry 2nd row in source table last 2 columns should be 6 hju
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39661079
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39661122
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:vijay11
ID: 39661170
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
 

Author Comment

by:vijay11
ID: 39661171
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
 

Author Comment

by:vijay11
ID: 39661174
Some have more than 10 parent child relation and some don't have any pls let me know
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39661223
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39661238
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39661268
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39661331
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question