#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    11
    Rep Power
    0

    Merging two rows


    Ok so I have two tables that I am joining in a join statement that works fine. I'm pulling the data that I want to pull just fine.

    SELECT table1.ID, table1.info1, table1.info2, table1.info3, table2.ExtraInfoName, table2.ExtraInfoValue
    FROM table1 LEFT OUTER JOIN table2 ON Table1ID = ID
    Let's say for this example let's say that I have table 1 with colums as such

    ID |info1 |info2| info3
    1 | a | b | c
    2 | z | y | x

    And I also have table 2 which has columns

    ExtraID | ExtraInfoName(nvarchar) |ExtraInfoValue(nvarchar) |Table1ID
    1 | name | david | 1
    2 | age | 88 | 1
    3 | name | steve | 2
    4 | age | 9 | 2

    So I get this as a result,

    ID| info1| info2 | info3 | ExtraInfoName | ExtraInfoValue
    1 | a | b | c | name | david
    1 | a | b | c | age | 88
    2 | z | y | x | name | steve
    2 | z | y | x | age | 9

    This is a problem for me because what I want to do with the data from this query cannot have duplicate ID but I need all the data from table2. So this is what I want it to look like.

    ID | info1| info2| info3 | ExtraInfoName |ExtraInfoValue |ExtraInfoName| ExtraInfoValue
    1 | a | b | c | name | david | age | 88
    2 | z | y | x | name | steve | age | 9
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    congratulations, you have just discovered one of the (many) drawbacks of the EAV (entity-attribute-value) anti-pattern

    do the cosmetic re-arrangement of merging the rows in your application language

    trying to do it with sql will bring only tears
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    congratulations, you have just discovered one of the (many) drawbacks of the EAV (entity-attribute-value) anti-pattern

    do the cosmetic re-arrangement of merging the rows in your application language

    trying to do it with sql will bring only tears
    I had a feeling that this was going to be the case, thanks for confirming so quickly.

IMN logo majestic logo threadwatch logo seochat tools logo