取消透视多列MSSQLServer

Question

我有一个MSSQLServer表，如下所示：

实际HSS>已批准Surg实际Amb><1232>

CASNumber	ClaimVersion	已批准HSS	实际Surg
GN00000068	1	819.64	190	120	1142

您可以使用VALUES表值构造函数和一个额外的APPLY运算符来取消透视表

表：

SELECT *
INTO Data
FROM (VALUES 
('GN00000068', 1, 819.64, 819.64, 190, 120, 1232, 1142)
) v (CASNumber, ClaimVersion, ActualHSS, ApprovedHSS, ActualSurg, ApprovedSurg, ActualAmb, ApprovedAmb)

声明：

SELECT d.CASNumber, d.ClaimVersion, v.Item, v.Actual, v.Approved
FROM Data d
CROSS APPLY (VALUES
('HSS', d.ActualHSS, d.ApprovedHSS),
('Surg', d.ActualSurg, d.ApprovedSurg),
('Amb', d.ActualAmb, d.ApprovedAmb)
) v (Item, Actual, Approved)

结果：

CASNumber  ClaimVersion Item  Actual  Approved
----------------------------------------------
GN00000068 1            HSS   819.64    819.64
GN00000068 1            Surg  190.00    120.00
GN00000068 1            Amb  1232.00   1142.00