SQL Server，限制UNPIVOT自动排序列 [英] SQL Server , restrict UNPIVOT to order columns automatically

问题描述

我有一张表格，其中的数据排成一行:

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

我正在努力寻找解决方案，该解决方案将限制UNPIVOT在默认情况下对列进行排序.我想按自己的方式订购这些专栏.这是查询:

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

输出为:

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

推荐答案

由于使用的是SQL Server 2008，因此可以使用CROSS APPLY和VALUES来取消数据的发布.使用此功能，您可以为排序顺序"创建一列:

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;

请参见带有演示的SQL小提琴

这篇关于SQL Server，限制UNPIVOT自动排序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！