Results 1 to 9 of 9

Thread: SQL Query HELP!

  1. #1
    Join Date
    May 2014
    Posts
    6

    Unanswered: SQL Query HELP!

    Hello,

    I need some help with a SQL query. I have a single table with two columns 'Child' and 'Parent'.

    The table looks like this
    Child
    Parent
    Yogurt
    Products
    Strawberry
    Yogurt
    Vanilla
    Yogurt
    Blueberry
    Yogurt
    Ice Cream
    Products
    Coffee
    Ice Cream
    Pecan
    Ice Cream
    Cookie
    Ice Cream

    I need to write a query to generate the following output.

    Child
    Parent
    Yogurt
    Products
    Strawberry
    Products
    Vanilla
    Products
    Blueberry
    Products
    Ice Cream
    Products
    Coffee
    Products
    Pecan
    Products
    Cookie
    Products
    Strawberry
    Yogurt
    Vanilla
    Yogurt
    Blueberry
    Yogurt
    Coffee
    Ice Cream
    Pecan
    Ice Cream
    Cookie
    Ice Cream

    Any suggestions on how to generate the output using SQL?

    Thank you!
    Attached Thumbnails Attached Thumbnails Screen Shot 2014-05-20 at 9.57.22 PM.png  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which version of SQL Server are you using? More importantly, what is the earliest version that you need to support?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2014
    Posts
    6
    It is SQL Server 2008 R2. Please have a look at the attachments since the table looks all funky.

    Thank you.
    Attached Thumbnails Attached Thumbnails Screen Shot 2014-05-20 at 9.57.22 PM.png   Screen Shot 2014-05-20 at 11.09.45 PM.png  

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT Child
         , Parent
     FROM  @table
     WHERE Parent <> 'Products'
    UNION ALL
    SELECT Child
         , 'Products'
     FROM  @table
    ;

  5. #5
    Join Date
    May 2014
    Posts
    6
    Thank you that worked!

  6. #6
    Join Date
    May 2014
    Posts
    6
    Hello tonkuma, I spoke a bit too soon and provided a very simplistic example of what I needed. Let's say there is another parent/child relationship that sits under the various Yogurts and Ice Creams. I have attached an example along with the desired output.

    Any ideas on how it would work?

    Thank you for your time.
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by buddan View Post
    Hello tonkuma, I spoke a bit too soon and provided a very simplistic example of what I needed. Let's say there is another parent/child relationship that sits under the various Yogurts and Ice Creams. I have attached an example along with the desired output.

    Any ideas on how it would work?

    Thank you for your time.
    Please provide the example in the body of your post by text format(of DDL(s) and INSERT statement(s) to populate them), like...

    Original table:
    Code:
    DECLARE @t TABLE 
    ( Child  VARCHAR(10) NOT NULL
    , Parent VARCHAR(10) NOT NULL
    )
    
    INSERT INTO @t
    ( Child , Parent )
    VALUES
      ( 'Yogurt'     , 'Products'  )
    , ( 'Strawberry' , 'Yogurt'    )
    , ( 'Vanilla'    , 'Yogurt'    )
    ...
    ...
    , ( '1pt-Cook'   , 'Cookie'    )
    , ( '1gal-Cook'  , 'Cookie'    )
    Expeced results:
    Code:
    Child      Parent
    ---------- ---------
    Strawberry Yogurt
    Vanilla    Yogurt
    ...
    ...
    Strawberry Products
    Vanilla    Products
    Then, others(including me) might understand your requirements enough, and could test their ideas easily.
    Last edited by tonkuma; 05-21-14 at 11:20.

  8. #8
    Join Date
    May 2014
    Posts
    6
    See post below.
    Last edited by buddan; 05-21-14 at 18:28. Reason: table came out incorrectly

  9. #9
    Join Date
    May 2014
    Posts
    6
    DECLARE @t TABLE
    ( Child VARCHAR(10) NOT NULL
    , Parent VARCHAR(10) NOT NULL
    )

    INSERT INTO @t
    ( Child , Parent )
    VALUES
    ( 'Yogurt' , 'Products' )
    , ( 'Strawberry' , 'Yogurt' )
    , ( 'Vanilla' , 'Yogurt' )
    , ( 'Blueberry' , 'Yogurt' )
    , ( '3oz-Straw' , 'Strawberry')
    , ( '6oz-Straw' , 'Strawberry')
    , ( '10oz-Straw' , 'Strawberry')
    , ( '3oz-Van' , 'Vanilla')
    , ( '6oz-Van' , 'Vanilla')
    , ( '10oz-Van' , 'Vanilla')
    , ( '3oz-Blue' , 'Blueberry')
    , ( '6oz-Blue' , 'Blueberry')
    , ( '10oz-Blue' , 'Blueberry')
    ( 'Ice Cream' , 'Products' )
    , ( 'Coffee' , 'Ice Cream' )
    , ( 'Pecan' , 'Ice Cream' )
    , ( 'Cookie' , 'Ice Cream' )
    , ( '10oz-Coff' , 'Coffee' )
    , ( '1pt-Coff' , 'Coffee' )
    , ( '1gal-Coff' , 'Coffee' )
    , ( '10oz-Pec' , 'Pecan' )
    , ( '1pt-Cook' , 'Pecan' )
    , ( '1gal-Cook' , 'Pecan' )
    , ( '10oz-Cook' , 'Cookie' )
    , ( '1pt-Cook' , 'Cookie' )
    , ( '1gal-Cook' , 'Cookie' )

    I was not able to post the desired output due to the length for some reason. I have attached the .pdf of the desired output.

    Thanks for the help.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •