CODEWITHSUNDEEP
sqljoinrow
Mikael
Mikael

Reputation: 15

SQL Join a row table with a column table

I have two tables that I’m trying to connect (join). Table1 is a column table as follows:

id Phone Name Description
101 123456 Maria Abc
102 234567 Daniel Def

Table 2 is a row table as follows:

id Attribute Value
101 Manager Rudolf
101 Account 456
101 Code B
102 Manager Anna
102 Code B
102 Code C

The result I’m looking for is:

id Phone Name Description Manager Account Code
101 123456 Maria Abc Rudolf 456 B
102 234567 Daniel Def Anna B,C

Upvotes: 0

Views: 227

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133370

You should use 3 times table 2 for get the 3 attributes inner for manager (alway matching) and left join for Account and CardNo (not always matching)

select a.id, a.phone, a.Name, a.Description b.value, c.value
from table1 a 
inner join table2 b ON a.id = b.id and b.attribute = 'Manager' 
left  join table2 c ON a.id = c.id and c.attribute = 'Account' 
left join table2 d ON a.id = d.id and d.attribute = 'CardNo'

Upvotes: 0

Eugene K
Eugene K

Reputation: 86

If you have only three unique attribute in your "row table" than you can use subqueries like this:

SELECT 
t.*,
(SELECT t2.Value FROM Table2 WHERE t2.id = t1.id AND t2.Attribute='Manager') as Manager,
(SELECT t2.Value FROM Table2 WHERE t2.id = t1.id AND t2.Attribute='Account') as Account, 
(SELECT t2.Value FROM Table2 WHERE t2.id = t1.id AND t2.Attribute='Cardno') as Cardno,
FROM Table1 t

If you have more unique attributes then you should try views or stored procedure and temporary table to generate the table that you want

Upvotes: 0

The Impaler
The Impaler

Reputation: 48780

You can join the same table thrice (using different aliases). For example:

select
  p.*,
  a.value as Manager,
  b.value as Account,
  c.value as Cardno
from table1 p
left join table2 a on a.id = p.id and a.attribute = 'Manager'
left join table2 b on b.id = p.id and b.attribute = 'Account'
left join table2 c on c.id = p.id and b.attribute = 'Cardno'

Upvotes: 1

Related Questions