How to unpivot this table

Question

I have created a temp table that I would like to unpivot it, but I keep getting an error message as follow: Msg 156, Level 15, State 1, Line 196 Incorrect syntax near the keyword 'for'.

My temp table is below:

Dossier Order Number T_Code Routine_Labour Variable_Labour

I would like to unpivot it to below:

I used the script below:

Select 
Dossier,
Order_Number,
T_Code,
Labour_Type,
Amount

from 
#LabourSplit

Unpivot 
(
 Amount for Labour_Type in (Routine_Labour, Variable_Labour)
 ) as LabourSplit_U

As soon as I run this script, the error message pop up. even I commented out the two columns in the temp table and in the script, the error message is still there.

Select 
Dossier,
--Order_Number,
--T_Code,
Labour_Type,
Amount

from 
#LabourSplit

Unpivot 
(
 Amount for Labour_Type in (Routine_Labour, Variable_Labour)
 ) as LabourSplit_U

Answer 1

Try this:

Select 
Dossier,
Order_Number,
T_Code,
Labour_Type,
Amount
FROM
(
  Select 
  Dossier,
  Order_Number,
  T_Code,
  Routine_Labour, 
  Variable_Labour
  from #LabourSplit
) a
Unpivot 
(
  Amount for Labour_Type in (Routine_Labour, Variable_Labour)
) as LabourSplit_U

SQL Fiddle

Results:

| DOSSIER | ORDER_NUMBER | T_CODE |     LABOUR_TYPE | AMOUNT |
|---------|--------------|--------|-----------------|--------|
|    1234 |         5678 |      1 |  Routine_Labour |     10 |
|    1234 |         5678 |      1 | Variable_Labour |     20 |
|    1234 |         3434 |      1 |  Routine_Labour |     20 |
|    1234 |         3434 |      1 | Variable_Labour |     70 |
|    1234 |         5671 |      1 |  Routine_Labour |     30 |
|    1234 |         5671 |      1 | Variable_Labour |     10 |
|    1234 |         3422 |      1 |  Routine_Labour |     40 |
|    1234 |         3422 |      1 | Variable_Labour |     40 |
|    1234 |         1122 |      1 |  Routine_Labour |     11 |
|    1234 |         1122 |      1 | Variable_Labour |     30 |

However, UNPIVOT as far as I know is supported in full versions of SQL Server. If you have a compact edition or other free versions you need to use UNION ALL:

Select 
Dossier,
Order_Number,
T_Code,
'Routine_Labour' AS Labour_Type,
Routine_Labour AS Amount
FROM LabourSplit
UNION ALL 
Select 
Dossier,
Order_Number,
T_Code,
'Variable_Labour' AS Labour_Type,
Variable_Labour AS Amount
FROM LabourSplit
ORDER BY 1,2,3

Check this SQL Fiddle

Results:

| DOSSIER | ORDER_NUMBER | T_CODE |     LABOUR_TYPE | AMOUNT |
|---------|--------------|--------|-----------------|--------|
|    1234 |         1122 |      1 |  Routine_Labour |     11 |
|    1234 |         1122 |      1 | Variable_Labour |     30 |
|    1234 |         3422 |      1 | Variable_Labour |     40 |
|    1234 |         3422 |      1 |  Routine_Labour |     40 |
|    1234 |         3434 |      1 |  Routine_Labour |     20 |
|    1234 |         3434 |      1 | Variable_Labour |     70 |
|    1234 |         5671 |      1 | Variable_Labour |     10 |
|    1234 |         5671 |      1 |  Routine_Labour |     30 |
|    1234 |         5678 |      1 |  Routine_Labour |     10 |
|    1234 |         5678 |      1 | Variable_Labour |     20 |