SQL Server , restrict UNPIVOT to order columns automatically

Question

I have a table with data in one row:

Account | OrderID   | OrderName          | Priority | Fasting   |AssignedTo       |ResultsTo    |Location
----------------------------------------------------------------------------------------------------------------------------
12345   | REQ123456 | Lipid Panel (1.2)  |Routine   | Yes       |Fast, Brook      |Nurse Group  |Fisher Rd, Woodbridge, NV

Now I want to UNPIVOT the data to show the user in the following form:

GROUPCOL    | LABEL       | VALUE
-------------------------------------------------
General     | Account     | 12345
General     | OrderID     | REQ123456
General     | OrderName   | Lipid Panel (1.2)
General     | Priority    | Routine    
General     | Fasting     | Yes        
Result      | ResultsTo   | Nurse Group
Result      | AssignedTo  | Fast, Brook
Result      | Location    | Fisher Rd, Woodbridge, NV

I am struggling to find the solution that will restrict UNPIVOT to sort the columns by default. I want to order the columns my way. Here is the query:

SELECT  'General' GROUPCOL, LABEL, VALUE
FROM TESTPIVOT
UNPIVOT (
    VALUE FOR LABEL IN (Account, OrderID, OrderName, Priority, Fasting)
) AS UNPVT
UNION
SELECT  'Result' GROUPCOL, LABEL, VALUE
FROM TESTPIVOT
UNPIVOT (
    VALUE FOR LABEL IN (AssignedTo, ResultsTo, Location)
) AS UNPVT

The output is:

GROUPCOL    | LABEL       | VALUE
-------------------------------------------------
General     | Account     | 12345
General     | Fasting     | Yes        
General     | OrderID     | REQ123456
General     | OrderName   | Lipid Panel (1.2)
General     | Priority    | Routine    
Result      | AssignedTo  | Fast, Brook
Result      | Location    | Fisher Rd, Woodbridge, NV 
Result      | ResultsTo   | Nurse Group

I thought, if somehow I have a order column associated with the columns then I can order it at the end as desired (that way, I can change the order as per the requirement).

Answer 1

Since you are using SQL Server 2008, then you can use CROSS APPLY and VALUES to UNPIVOT data. Using this will allow you to create a column for Sorting Order:

select c.GroupCol, c.Label, c.Value
from testpivot
cross apply
(
  values
  ('General', 'Account', Account, 1),
  ('General', 'OrderID', OrderID, 2),
  ('General', 'OrderName', OrderName, 3),
  ('General', 'Priority', Priority, 4),
  ('General', 'Fasting', Fasting, 5),
  ('Result', 'ResultsTo', ResultsTo, 6),
  ('Result', 'AssignedTo', AssignedTo, 7),
  ('Result', 'Location', Location, 8)
) c (GroupCol, Label, Value, SortOrder)
order by sortorder;

See SQL Fiddle with Demo