Create View based on join of column name and table value of two tables

I got two tables.

Table Ah:

Country HDI Luck SomeOtherColumn SoOn

1       1   2    x               y
2       1   2    b               c
3       2   3    v               g
4       3   4    e               y
5       2   2    b               g
6       4   1    n               k

and a second

Table Bu

Attribute Value Meaning
Country   1     RichAndLuckyCountry
Country   2     AnotherRichAndLuckyCountry
Country   3     AlsoQuiteRichButStillLuckyCountry
Country   4     NotSoRichAndSadAboutItCountry
Country   5     DoingWellCountry
Country   6     DontWorryBeHappyCountry
HDI       1     Very high
HDI       2     High
HDI       3     Medium
HDI       4     Low
Luck      1     Very high
Luck      2     High
Luck      3     Medium
Luck      4     Low

The resulting View I need would look like this:

Table Result

Country Country_Dissolved                  HDI HDI_Dissolved Luck Luck_Dissolved SomeOtherColumn SoOn
1       RichAndLuckyCountry                1   Very high     2    High           x               y
2       AnotherRichAndLuckyCountry         1   Very high     2    High           b               c
3       AlsoQuiteRichButStillLuckyCountry 2   High          3    Medium         v               g
4       NotSoRichAndSadAboutItCountry      3   Medium        4    Low            e               y
5       DoingWellCountry                   2   High          2    High           b               g
6       DontWorryBeHappyCountry            4   Low           1    Very High      n               k   

I only managed to get it done with one column combined with a where clause:

CREATE OR REPLACE VIEW Result AS
SELECT Ah.Country. Bu.Meaning as County_Dissolved
FROM Ah
INNER JOIN Bu
ON Ah.Country = Bu.Value
WHERE Bu.Attribute = 'Country'

I would probably need some command which loop through the column names and joins column names with the values in the attribute column, because the real table has many more possible combinations,so just making multiple SQL statements for every case is no solution.

How can I create a view like the above Result Table? Dissolve the values of Table Ah with keys in Table Bu.

SQL Fiddle.

Any help would be appreciated. Thanks in advance!

Answer

You need 3 left joins of Ah to Bu:

SELECT Ah.Country,
  MAX(CASE WHEN b1.Attribute = 'Country' THEN b1.Meaning END) Country_Dissolved,
  Ah.HDI,
  MAX(CASE WHEN b2.Attribute = 'HDI' THEN b2.Meaning END) HDI_Dissolved,
  Ah.Luck,
  MAX(CASE WHEN b3.Attribute = 'Luck' THEN b3.Meaning END) Luck_Dissolved,
  Ah.SomeOtherColumn, 
  Ah.SoOn
FROM Ah
LEFT JOIN Bu b1 ON b1.Value = Ah.Country AND b1.Attribute = 'Country'
LEFT JOIN Bu b2 ON b2.Value = Ah.HDI AND b2.Attribute = 'HDI'
LEFT JOIN Bu b3 ON b3.Value = Ah.Luck AND b3.Attribute = 'Luck'
GROUP BY Ah.Country, Ah.HDI, Ah.Luck, Ah.SomeOtherColumn, Ah.SoOn
ORDER BY Ah.Country

See the demo.