Crosstab/Pivot query in TSQL on nvarchar columns

Question

I have a Table1:

ID  Property
1   Name
2   City
3   Designation

and Table2:

ID  RecordID  Table1ID  Value
1   1         1         David
2   1         2         Tokyo
3   2         1         Scott
4   2         3         Manager

The Table1ID of Table2 maps to Table1's ID. Now I wish to show the Table1 Property column values as column headers and have a result set in format like:

RecordID     Name    City    Designation
1            David   Tokyo   NULL
2            Scott   NULL    Manager

What is the best/efficient way to achieve this in T-SQL considering that the number of records in Table1 (i.e. the columns in result set) can change and thus should be handled dynamically.

Although I tried PIVOT and CASE based queries, but have been struggling with both of them. :(

Any help/guidance would be appreciated.

Thanks!

Update:
I've been able to create the dynamic query, but one thing which I am still not able to understand is why MAX has been used in the CASE statements. Kindly ignore my noobness.

Answer 1

Use:

  SELECT t2.recordid,
         MAX(CASE WHEN t1.property = 'Name' THEN t2.value END) AS name,
         MAX(CASE WHEN t1.property = 'City' THEN t2.value END) AS city,
         MAX(CASE WHEN t1.property = 'Designation' THEN t2.value END) AS designation
    FROM TABLE2 t2
    JOIN TABLE1 t1 ON t1.id = t2.table1id
GROUP BY t2.recordid
ORDER BY t2.recordid