Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2003
    Posts
    54

    Talking Unanswered: Query to represent denormalization of data

    Hi All,

    Bit of a funny one. I have two tables, lets call them PetOwner and Pets

    Code:
    PetOwner
    
    OwnerID    OwnerName
    1          Tim
    2          Paul
    3          Andy
    
    
    Pets
    OwnerID    PetName
    1          Tricia
    1          Louise
    2          Bill
    2          Ben
    2          David
    2          Topsy
    3          Flopsy
    3          Mopsy
    And I need to write a query that returns a resultset like this

    Code:
    OwnerID    OwnerName   PetName1    PetName2   PetName3   PetName4   PetName5
    1          Tim         Tricia      Louise     NULL       NULL       NULL   
    2          Paul        Bill        Ben        David      Topsy      Flopsy
    3          Andy        Flopsy      Mopsy      NULL       NULL       NULL
    Can anyone help?

    N.B. A pet owner can only have a maximum of 5 pets, but even if no-one in the database has 5 pets, the PetName5 column must still appear (but have a value of NULL).

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this SQL 2000 or 2005?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2003
    Posts
    54
    Doh! Sorry, SQL Server 2005.

  4. #4
    Join Date
    Apr 2003
    Posts
    54
    No it's not, it's 2000! (soon to be moved to 2005)

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No probs

    And absolutely always a max of five? This affects the best\ easiest solution....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Soon enouigh to accept a 2005 answer?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This should work in 2000 and 2005. There is a more efficient 2005 method so you can update it when you migrate.

    Code:
     
    SELECT  po.OwnerID
            , po.OwnerName
            , MAX(CASE WHEN rn = 1 THEN PetName END) AS PetName1
            , MAX(CASE WHEN rn = 2 THEN PetName END) AS PetName2
            , MAX(CASE WHEN rn = 3 THEN PetName END) AS PetName3
            , MAX(CASE WHEN rn = 4 THEN PetName END) AS PetName4
            , MAX(CASE WHEN rn = 5 THEN PetName END) AS PetName5
    FROM    (SELECT *
                    ,   (SELECT COUNT(*) AS rec_no
                        FROM    dbo.pets AS p2
                        WHERE   p2.OwnerID = p.OwnerID
                                AND p2.PetName <= p.PetName) AS rn
            FROM    dbo.pets AS p) AS row_numbers
    INNER JOIN
            dbo.PetOwner AS po
    ON  po.OwnerID = row_numbers.OwnerID
    GROUP BY po.OwnerID
            , po.OwnerName
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2003
    Posts
    54
    That's great, I'll test it and let you know how I get on.

    Cheers,

  9. #9
    Join Date
    Apr 2003
    Posts
    54
    Ok, I'm british so I shouldn't say this, but pootle flump, you rock! Thanks, it works a dream

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by macca
    Ok, I'm british so I shouldn't say this, but pootle flump, you rock! Thanks, it works a dream
    I'm British too so Jolly Good Show!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't British people rock too? Elton? Ziggie? Mick?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Don't British people rock too? Elton? Ziggie? Mick?
    ... Poots?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Elton... Rock... Rock... Elton?
    Seriously!
    George
    Home | Blog

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Elton "crocodile" rocks...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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