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.