PIVOT with 3 tables and JOIN without number values

Hope someone can help me or point me to the right direction. I’m more of a beginner in sql language.

I have 3 table users, dataTypes and usersData

**users**           **dataTypes**                **usersData**
id                  id                           id
dateCreated         name (varchar)               idUsers
                    description (varchar)        idDataTypes

I only managed to use join on three tables like this:

select users.*, usersData.*, dataTypes.* from users left join (usersData inner join dataTypes on usersData.idDataTypes=dataTypes.id) on users.id=usersData.idUsers order by users.id asc, dataTypes.id asc

That gets all the entries for every single user like this and then repeating multiple times the same user id:

**users.id**    **usersData.data**    **dataTypes.name**

However I want for every data type to appear as a column and every row would have all entered user data. Something like this:

**Name**   **Last Name**   **Address**
John       Smith           New York
Tom        NULL            NULL

Looking on the internet, pivot is used to have row values as columns but the sql code is meant as aggregate (sum, min, max). Don’t know if this is the right approach with pivot or perhaps my tables are not properly organized.

Answer

I suspect that you want conditional aggregation to pivot your EAV model. The idea is:

select u.id, 
    max(case when dt.name = 'name'      then ud.data end) as name,
    max(case when dt.name = 'last name' then ud.data end) as name,
    max(case when dt.name = 'address'   then ud.data end) as address
from users u 
left join usersdata ud on u.id = ud.idusers
left join datatypes dt on ud.iddatatypes = dt.id
group by u.id