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).

What version of SQL Server are you using?? – Taryn Jun 18 '13 at 18:24 — Taryn, Jun 18 '13 at 18:24
I am using SQL Server 2008 R2 – Sri Reddy Jun 18 '13 at 18:25 — Sri Reddy, Jun 18 '13 at 18:25

score 6 · Accepted Answer · answered Jun 18 '13 at 18:30

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

it is a great solution. I didn't know the versatility of CROSS APPLY. This seems to be a better solution where you can specify your order explicitly. I think I don't even need the sort order column unless, I give user an ability to change the sort order. Excellent! — Sri Reddy, Jun 18 '13 at 18:42

SQL Server , restrict UNPIVOT to order columns automatically

1 Answers1

Linked