SQL rotate results from wide to vertical

I would love some help with the best way to capture some column data and rotate it so I can store the column name and numeric value in a temp table.

The results are a single row showing a value for the columns listed here:

AccountingCode ActiveCostAllocationCode1Segment1 ActiveCostAllocationCode1Segment1Description
-------------- --------------------------------- --------------------------------------------
    0              71                                264                                         

I would like to take the above query and rotate the output to look more vertical.

ColName                                          Value
--------------------------------------------- ---------
AccountingCode                                      0
ActiveCostAllocationCode1Segment1                  71
ActiveCostAllocationCode1Segment1Description      264

I was trying to use PIVOT / UNPIVOT but could not figure how to make it work for this case.

Any ideas?

Answer

If you are working with SQL Sever then you can use APPLY :

SELECT tt.ColName, tt.val
FROM table t CROSS APPLY
     ( VALUES ('AccountingCode', AccountingCode), 
              ('ActiveCostAllocationCode1Segment1', ActiveCostAllocationCode1Segment1),
              ('ActiveCostAllocationCode1Segment1Description', ActiveCostAllocationCode1Segment1Description)    
     ) tt(ColName, Val);

In standard you can use UNION ALL to UNPIVOT the data.