Unpivot certain columns to certain row

Question

I have a table with the following structure:

ID	UID	C304	C305	C304_Value	C305_Value
1	1225	86	01	15	99
2	1226	89	06	10	15

I would like to have the output to be something like

ID	UID	Col	Col_Value	Value
1	1225	C304	86	99
1	1225	C305	89	15

I've tried unpivot, but it will duplicate the columns per a row.

Is there a way to achieve this?

This is my SQL:

SELECT UID, Col, Col_Value,
C304_Value, C305_Value
--,*
FROM Input
--Unpivot(  Field_Values    
--              for field IN ([dfdf])) as PVT
unpivot (
Col_Value
for Col in (C304, C305)
) up
where UID = '1225'

Answer 1

I suspect that you just want to unpivot the values. I'm not sure what the last column is, but the basic idea is:

select t.id, t.uid, v.*
from t cross apply
     (values ('C304', C304),
             ('C305', C305)
     ) v(col, col_value);

This does not return the results you specify, but I think it is what you are trying to do.

If the last column is just the "other" value, you would include that as well:

select t.id, t.uid, v.*
from t cross apply
     (values ('C304', C304, C305),
             ('C305', C305, C304)
     ) v(col, col_value, other_value);