T-SQL : select table with columnames derived from fieldName column (PIVOT, CASE)

Question

I'm struggling with composing a select statement in T-SQL that generates a table which column names are representing the value of a source table column (FieldName) and records with values which are in column (FieldValue) and grouped by a foreign key (RegistrationId).

I found a lot of examples with similar problems and I'm surprised I couldn't find the correct solution, as this seems to me a very common situation.

|RegistrationID    |FieldName|FieldValue             |
+------------------+---------+-----------------------+
|Guid1             |firstname|john                   |
|Guid1             |lastname |johnson                |
|Guid1             |email    |john.johnson@mail.com  |
|Guid2             |firstname|mary                   | 
|Guid2             |lastname |williams               |
|Guid2             |email    |mary.williams@mail.com |
|Guid3             |firstname|james                  |
|Guid3             |lastname |miller                 |
|Guid3             |email    |james.miller@mail.com  |
|Guid4             |firstname|patricia               |
|Guid4             |lastname |jones                  |
|Guid4             |email    |patricia.jones@mail.com|

Resulting table

|RegistrationID    |firstname|lastname          |email                   |
+------------------+---------+------------------+------------------------+
|Guid1             |john     |johnson           |john.johnson@mail.com   |
|Guid2             |mary     |williams          |mary.williams@mail.com  |
|Guid3             |james    |miller            |james.miller@mail.com   |
|Guid4             |patricia |jones             |patricia.jones@mail.com |

I tried with pivot and case statements but the result was far from satisfying.

Any guidance is much appreciated

PS. names are randomly generated, so any reference to real persons is just by coincidence.

Answer 1

I would suggest using conditional aggregation to pivot this entity-attribute-value table:

select
    registrationID,
    max(case when fieldName = 'firstname' then fieldValue end) firstname,
    max(case when fieldName = 'lastname'  then fieldValue end) lastname,
    max(case when fieldName = 'email'     then fieldValue end) email
from mytable
group by registrationID