Convert a few columns in the table to a resultset of name, value pair rows in SQL Server

Question

I have a table like this

Table1

Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
1    2    3    4    5    6    7    8
2    2    3    4    5    16   17   18
3    2    3    4    5    26   27   28
4    2    3    4    5    36   37   38

I want the data to be converted in this format

Col1   DerivedCol1  DerivedCol2
1      Col6         6
1      Col7         7
1      Col8         8
2      Col6         16
2      Col7         17
2      Col8         18
3      Col6         26
3      Col7         27
3      Col8         28
4      Col6         36
4      Col7         37
4      Col8         38

Edit: One more important aspect in the question is that i dont know how many columns that i am going to choose to make them as Name, Value pairs and that might change at run time.

Answer 1

Select Col1, 'Col6', Col6
From Table
Union All
Select Col1, 'Col7', Col7
From Table
Union All
Select Col1, 'Col8', Col8
From Table

There is no dynamic means of doing this other than to build the SQL statement in code.

Answer 2

I would take a look an pivot/unpivot here

Answer 3

You could build something like the following in Dynamic SQL

WITH pvt (Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8 )
AS
(

SELECT 1,2,3,4,5,6,7,8 UNION ALL
SELECT 2,2,3,4,5,16,17,18 UNION ALL
SELECT 3,2,3,4,5,26,27,28 UNION ALL
SELECT 4,2,3,4,5,36,37,38
)

SELECT Col1, DerivedCol1 ,DerivedCol2
FROM  pvt
UNPIVOT
   (DerivedCol2  FOR DerivedCol1   IN 
      (Col6 , Col7, Col8  )
)AS unpvt;

Returns

Col1        DerivedCol1   DerivedCol2

1           Col6               6
1           Col7               7
1           Col8               8
2           Col6               16
2           Col7               17
2           Col8               18
3           Col6               26
3           Col7               27
3           Col8               28
4           Col6               36
4           Col7               37
4           Col8               38