Transpose row values to columns ( join table with a bag of key value pairs in T-SQL)

Question

I have two tables, table1 ( Id, date, info1) table2 ( Id, date, nvarchar(50) Key, nvarchar(50) Value)

I would like to join these tables and obtain rows where each value in the Key column is a new column, and the values in the value table are the data in the rows.

Example:

table1 row:

1, 2010-01-01, 234

table2 row:

1, 2010-01-01, 'TimeToProcess', '15'
1, 2010-01-01, 'ProcessingStatus', 'Complete'

The result desired is a row like:

1, 2010-01-01, 234, '15', 'Complete'

Where the column headers are (Id, date, info1, TimeToProcess, ProcessingStatus)

This transposition looks like it works similarly to PIVOT but I could not get it to work -- I suspect -- due to the nvarchar(50) type of the Key, Value columns and the fact that I am forced to use an aggregate function when in fact I do not need it.

I could use inner joins to achieve this but the only way I know how to do it would take 1 inner join per Key which in my case amounts to 6 inner joins as that's how many metrics I have.

How can I do this?

Answer 1

You're on the right track with PIVOT. You can PIVOT on a string value. Use the MIN or MAX aggregate function.