SQL to retrieve data using column values ​​from one table as columns of another table

Question

I have these two tables:

Org_Extra_Attr

org_id      attr_name       attr_path
1           desk_name       str1
1           citizen         bool1           
2           perm_user       bool1           
3           skype_id        str1            
3           twitter         str2

User_Attr_Values

org_id      user_id     str1    str2    str3    str4    bool1   bool2   bool3   bool4
1           1           b1d07   null    null    null    1       null    null    null
1           2           b2d01   null    null    null    0       null    null    null
2           3           null    null    null    null    1       null    null    null
2           4           null    null    null    null    1       null    null    null
3           5           sam_sky sam_twt null    null    null    null    null    null
3           6           tom_sky tom_twt null    null    null    null    null    null

So, here each of them should specify max.of 4 additional attributes of type String and Boolean, the table Org_Extra_Attr is similar to metadata. For example, org_id 1 has defined package_name, which will be str1 for its users, while org_id 3 has skype_id, which will be str1 for its users.

This may be a bad design, but for now I need to get the names and values ​​of user attributes for a given org_id. As for org_id = 1, I need the result of the SQL query (and not the third table), as shown below:

user_id     attr_name   val
1           desk_name   b1d07
1           citizen     1
2           desk_name   b2d01
2           citizen     0

For org_id = 3

user_id     attr_name   val
5           skype_id    sam_sky
5           twitter     sam_twt
6           skype_id    tom_sky
6           twitter     tom_twt

Answer 1

-

select 
t2.user_id,
t1.attr_name,
    CASE
    WHEN t1.attr_path='str1' then t2.str1
    WHEN t1.attr_path='str2' then t2.str2
    WHEN t1.attr_path='str3' then t2.str3
    WHEN t1.attr_path='str4' then t2.str4
    WHEN t1.attr_path='bool1' then t2.bool1
    WHEN t1.attr_path='bool2' then t2.bool2
    WHEN t1.attr_path='bool3' then t2.bool3
    WHEN t1.attr_path='bool4' then t2.bool4
END attr_value
FROM org_Extra_attr t1 inner join User_Attr_Values t2
on t1.org_id = t2.org_id
where t1.org_id=1

Answer 2

,

. .

:

Org_Attr (PK = org_id + attr_no)

org_id   attr_no   attr_name   type
1        1         desk_name   STRING
1        2         citizen     BOOL
2        1         perm_user   BOOL
3        1         skype_id    STRING
3        2         twitter     STRING

Org_Attr_User (PK = org_id + attr_no + user_id)

org_id   attr_no   user_id   value
1        1         1         b1d07   
1        1         2         b2d01   
1        2         1         1
1        2         2         0
2        1         3         1
2        1         4         1
3        1         5         sam_sky    
3        1         6         tom_sky   
3        2         5         sam_twt   
3        2         6         tom_twt

, :

select oau.user_id, oa.attr_name, oau.value
from Org_Attr oa
join Org_Attr_User oau using (org_id, attr_no)
where org_id = 1;