sql for each row in a select generate multiple inserts to another table

Question

Is there a way to do the following?

Suppose a select from Table A had a few columns like this:

id | value1 | value2 | value3
=============================
1  |  1.0   |  2.0   |  3.0
2  |  1.5   |  1.6   |  1.7

I want to take the results of this and insert into another table (Table B) like this:

tableAId | key    | value
=========================
1        | value1 |  1.0
1        | value2 |  2.0
1        | value3 |  3.0
2        | value1 |  1.5
2        | value2 |  1.6
2        | value3 |  1.7

Is there a way to perform this in a way that I can do this in one insert select?

Answer 1

You can use unpivotto do this:

-- insert into TableB (tableAId, key, value)
select id, [key], value 
from (
    select id, [key], value 
    from TableA
    unpivot (
       [Value] for [Key] in ([Value1],[Value2],[Value3])
    ) as unpvt
) derived

This has the column names hardcoded but it would be easy to make it dynamic if needed.