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.



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

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.