TSQL 幫助我虛弱的頭腦在兩列上掌握 Pivot/Unpivot

一段時間以來，我一直試圖用我虛弱的頭腦來解決這個問題，但我遇到了困難。我已經查看了一些其他範例，例如SE.DBA答案，但我很難將其應用於我的確切情況。我不在乎解決方案是使用 PIVOT/UNPIVOT 還是 Cross Apply。

我使用 GROUP BY 是因為每個工資單可能有 15-45 條記錄，我希望每個工資單匯總所有這些記錄。我正在使用 SQLServer 2016。

下面的 DDL 和數據顯示了我目前擁有的源數據和 PIVOT 查詢

--Source data table
CREATE TABLE #EmployeeTaxes
(
    Employee int
   ,Payroll int
   ,FIT  DECIMAL(19,2)
   ,Employee_FICA DECIMAL(19,2)
   ,Employer_FICA DECIMAL(19,2)
   ,Employee_MEDI DECIMAL(19,2)
   ,Employer_MEDI DECIMAL(19,2)
   ,FIT_Gross  DECIMAL(19,2)
   ,Employee_FICA_Gross DECIMAL(19,2)
   ,Employer_FICA_Gross DECIMAL(19,2)
   ,Employee_MEDI_Gross DECIMAL(19,2)
   ,Employer_MEDI_Gross DECIMAL(19,2)
)

--Data in source table
INSERT INTO #EmployeeTaxes
VALUES (1, 1, 100.00, 150.00, 50.00, 200.00, 200.00,  20000.00, 20000.00, 20000.00, 20000.00, 20000.00)
     ,(2, 1, 100.00, 150.00, 50.00, 200.00, 200.00,  20000.00, 20000.00, 20000.00, 20000.00, 20000.00)
     ,(3, 1, 100.00, 150.00, 50.00, 200.00, 200.00,  20000.00, 20000.00, 20000.00, 20000.00, 20000.00)
     ,(1, 2, 200.00, 250.00, 150.00, 300.00, 300.00, 25000.00, 25000.00, 25000.00, 25000.00, 25000.00)
     ,(2, 2, 200.00, 250.00, 150.00, 300.00, 300.00, 25000.00, 25000.00, 25000.00, 25000.00, 25000.00)
     ,(3, 2, 200.00, 250.00, 150.00, 300.00, 300.00, 25000.00, 25000.00, 25000.00, 25000.00, 25000.00)
     ,(1, 3, 300.00, 350.00, 250.00, 400.00, 400.00, 30000.00, 30000.00, 30000.00, 30000.00, 30000.00)
     ,(2, 3, 300.00, 350.00, 250.00, 400.00, 400.00, 30000.00, 30000.00, 30000.00, 30000.00, 30000.00)
     ,(3, 3, 300.00, 350.00, 250.00, 400.00, 400.00, 30000.00, 30000.00, 30000.00, 30000.00, 30000.00)


--Basic data layout as it currently is
SELECT PAYROLL
   ,sum(fit)                       AS SumFIT
   ,sum(fit_gross)                 AS SumFIT_Gross
   ,sum(employee_fica)             AS Sum_EE_FICA
   ,sum(employee_fica_gross)       AS Sum_EE_FICA_Gross
   ,sum(employer_fica)             AS Sum_ER_FICA
   ,sum(employer_fica_gross)       AS Sum_ER_FICA_Gross
   ,sum(employee_medi)             AS Sum_EE_Medi
   ,sum(employee_medi_gross)       AS Sum_EE_Medi_Gross
   ,sum(employer_medi)             AS Sum_ER_Medi
   ,sum(employer_medi_gross)       AS Sum_ER_Medi_Gross
FROM #EmployeeTaxes
GROUP BY Payroll

--The query as I have it now, missing the Gross column
SELECT PAYROLL
     ,[Taxes]
     ,[Tax]      
FROM 
(
SELECT PAYROLL
   ,sum(fit)                       AS SumFIT
   ,sum(fit_gross)                 AS SumFIT_Gross
   ,sum(employee_fica)             AS Sum_EE_FICA
   ,sum(employee_fica_gross)       AS Sum_EE_FICA_Gross
   ,sum(employer_fica)             AS Sum_ER_FICA
   ,sum(employer_fica_gross)       AS Sum_ER_FICA_Gross
   ,sum(employee_medi)             AS Sum_EE_Medi
   ,sum(employee_medi_gross)       AS Sum_EE_Medi_Gross
   ,sum(employer_medi)             AS Sum_ER_Medi
   ,sum(employer_medi_gross)       AS Sum_ER_Medi_Gross
FROM #EmployeeTaxes
GROUP BY Payroll
) CK
UNPIVOT
(
   Tax FOR Taxes  IN (SumFIT
                     ,Sum_EE_FICA
                     ,Sum_ER_FICA
                     ,Sum_EE_Medi
                     ,Sum_ER_Medi)

) AS Unpvt

ORDER BY PAYROLL
DROP TABLE #EmployeeTaxes

我想要做的是將最後標有“_gross”的列放在它們自己的單獨列中，如下面的假數據所示

--Fake table that holds structure of desired output
CREATE TABLE #FakeDesiredOutput
(
    Payroll int
   ,Witholding varchar(100)
   ,Tax DECIMAL(19,2)
   ,Gross DECIMAL(19,2)
)
INSERT INTO #FakeDesiredOutput
VALUES (1,'FIT', 300.00, 60000.00)
     ,(1,'Employee_FICA', 450.00, 60000.00)
     ,(1,'Employer_FICA', 150.00, 60000.00)
     ,(1,'Employee_MEDI', 600.00, 60000.00)
     ,(1,'Employer_MEDI', 600.00, 60000.00)
     ,(2,'FIT', 600.00, 75000.00)
     ,(2,'Employee_FICA', 750.00, 75000.00)
     ,(2,'Employer_FICA', 450.00, 75000.00)
     ,(2,'Employee_MEDI', 900.00, 75000.00)
     ,(2,'Employer_MEDI', 900.00, 75000.00) 
     ,(3,'FIT', 900.00, 90000.00)
     ,(3,'Employee_FICA', 1050.00, 90000.00)
     ,(3,'Employer_FICA', 750.00, 90000.00)
     ,(3,'Employee_MEDI', 1200.00, 90000.00)
     ,(3,'Employer_MEDI', 1200.00, 90000.00)

SELECT * FROM #FakeDesiredOutput
DROP TABLE #FakeDesiredOutput

CROSS APPLY這樣做的方法是：

SELECT et.Payroll
   , v.Item
   , Tax = SUM(v.TaxValue)
   , Gross = SUM(v.GrossValue)
FROM #EmployeeTaxes et
CROSS APPLY (
   VALUES ('FIT', fit, fit_Gross, 1)
       , ('Employee_FICA', Employee_FICA, Employee_FICA_Gross, 2)
       , ('Employer_FICA', Employer_FICA, Employer_FICA_Gross, 3)
       , ('Employee_MEDI', Employee_MEDI, Employee_MEDI_Gross, 4)
       , ('Employer_MEDI', Employer_MEDI, Employer_MEDI_Gross, 5)
   )v(Item, TaxValue, GrossValue, OrderByNum)
GROUP BY et.Payroll
   , v.Item
   , v.OrderByNum
ORDER BY et.Payroll
   , v.OrderByNum;

結果：

+---------+---------------+---------+----------+
| 工資單| 項目 | 稅務 | 毛額 |
+---------+---------------+---------+----------+
| 1 | 散客 | 300.00 | 60000.00 |
| 1 | 員工_FICA | 450.00 | 60000.00 |
| 1 | 雇主_FICA | 150.00 | 60000.00 |
| 1 | 員工_MEDI | 600.00 | 60000.00 |
| 1 | 雇主_MEDI | 600.00 | 60000.00 |
| 2 | 散客 | 600.00 | 75000.00 |
| 2 | 員工_FICA | 750.00 | 75000.00 |
| 2 | 雇主_FICA | 450.00 | 75000.00 |
| 2 | 員工_MEDI | 900.00 | 75000.00 |
| 2 | 雇主_MEDI | 900.00 | 75000.00 |
| 3 | 散客 | 900.00 | 90000.00 |
| 3 | 員工_FICA | 1050.00 | 90000.00 |
| 3 | 雇主_FICA | 750.00 | 90000.00 |
| 3 | 員工_MEDI | 1200.00 | 90000.00 |
| 3 | 雇主_MEDI | 1200.00 | 90000.00 |
+---------+---------------+---------+----------+

該CROSS APPLY子句有第四列，OrderByNum只是為了讓結果反映您想要的排序順序，排在FIT其他條目之前。

引用自：https://dba.stackexchange.com/questions/162667