SQL Query From Same Table using external Table value

I am using SQL Server 2012. I have data in a SQL Server database in the same table that needs to be brought into multiple columns.

I have two tables, tblIVContent:

and TBLIVTextData:

In order for a record to show up in TBLIVTextData, when the data is created in our UI, it creates a Content_id on module_id equals 84.

I would need automatically find any record in TBLIVContent that has a module_id of 84 to show up in a query that queries against tblIVTextData

The columns I need is FirstName will always have a Field_id of 531, Lastname will be field_id of 543 and so on.

End states I would need the following output

 FirstName     |      LastName.    |       JobTitle    |

Record 1 would be as follows:

• FirstName equals Content_id of 263229 and field_id of 531
• LastName equals Content_id of 263229 and field_id of 543
• JobTitle equals Content_id of 263229 and field_id of 544

I’m not even sure how to do this. I don’t feel that joins are the right way to go? Can someone lead me down the right path?

I don’t necessarily want the answer (trying to learn here) but what’s the right way to begin this? Google returned a lot but I am not sure it is what I need.

Answer

Use conditional aggregation:

select
    content_id,
    max(case when d.field_id = 531 then field_value end) firstName,
    max(case when d.field_id = 543 then field_value end) lastName,
    max(case when d.field_id = 544 then field_value end) jobTitle
from TBLIVTextData d
inner join tblIVContent c on c.content_id = d.content_id
where c.module_id = 84
group by content_id