我有一张表格,其中只有一行数据:
Account | OrderID | OrderName | Priority | Fasting |AssignedTo |ResultsTo |Location
----------------------------------------------------------------------------------------------------------------------------
12345 | REQ123456 | Lipid Panel (1.2) |Routine | Yes |Fast, Brook |Nurse Group |Fisher Rd, Woodbridge, NV
现在我想要对数据进行UNPIVOT,以以下形式向用户展示:
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按列排序。我想按照自己的方式对列进行排序。以下是查询:
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
我觉得,如果在列中关联一个顺序列,那么就可以在最后按照需要对其进行排序(这样一来,我可以根据要求随时更改顺序)。