使用 PIVOT 轉換多行多列

所以我目前的查詢輸出如下：

我想將所有這些行合併為一長行，如下所示：

MyID    Customer  relc_rate  relc_consumption   relc_daysofservice   relc_unitmeasure   relc_charge   relc_chargedate
-----   --------  ---------  ----------------   ------------------   ----------------   -----------   ---------------
10016    112992    110W            753                  31                 KWH

並且幾乎在一行中重複了 RGAS、RWTR 等。是的，我知道如果我將所有服務類型合併到它自己的列中，這將是大約 50 列。

現在，我可以開始使用此 Pivot 查詢將服務編號輸入“類型”。但是，在那之後我陷入了困境，試圖弄清楚如何將每種服務類型以及費率等放入它自己的列中。

select *
from 
( 
SELECT [Premise]
   , [Premise Address] premise_address
   , [Customer] as customer_no
   , [Service Type]
   , [Service Number]

FROM UTIL_MAY2019
) premise_table
pivot 
(
 sum([Service number])
 for [Service Type] in ([RELC], [RWTR], [RSWR], [RGAR], [STRM], [DELC], [SLCM], [CGAS], [CWTR], [CSWR], [CGAR])
) piv_premise

這是用於測試的 DDL：

CREATE TABLE supercharge (
MyID int
, customer int 
, Servicetype varchar(10)
, servicerate varchar(10)
, serviceNumber int
, consumption int
, daysofservice int
, unitofmeasure varchar(10)
, chargeAmount double
, chargeDate datetime  
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate) 

VALUES 
(10016, 112992, RELC, '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'), 
(10016, 112992, RGAS, '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'), 
(10016, 112992, RWTR, '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'), 
(10016, 112992, RSWR, '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'), 
(10016, 112992, RGAR, '', 500, null, null, '',32.12, '2019-05-08'), 
(10016, 112992, STRM, '', 700, null, null, '',2.38, '2019-05-08'), 
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'), 
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');

好吧，這個解決方案並不是特別複雜（它不使用PIVOT），但它可以解決您的問題。可能有更聰明的方法來解決這個問題，但這裡有一種方法。

該解決方案從派生unpivoted表中選擇（注意關於 unpivot 原始數據的註釋）。它按分組MyId並customer使用CASE表達式來派生各個列的名稱和值。我的範例僅顯示servicetype了分成單獨列的兩列，但希望您會看到這種模式。（我不確定您如何處理帶有空白的行servicetype）。

--demo setup
DROP TABLE IF EXISTS supercharge
GO
CREATE TABLE supercharge (
MyID int
, customer int 
, Servicetype varchar(10)
, servicerate varchar(10)
, serviceNumber int
, consumption int
, daysofservice int
, unitofmeasure varchar(10)
, chargeAmount decimal(11,2)
, chargeDate datetime  
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate) 

VALUES 
(10016, 112992, 'RELC', '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'), 
(10016, 112992, 'RGAS', '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'), 
(10016, 112992, 'RWTR', '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'), 
(10016, 112992, 'RSWR', '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'), 
(10016, 112992, 'RGAR', '', 500, null, null, '',32.12, '2019-05-08'), 
(10016, 112992, 'STRM', '', 700, null, null, '',2.38, '2019-05-08'), 
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'), 
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');

--solution
SELECT MyID
   ,customer
--RELC columns
   ,max(CASE 
           WHEN Servicetype = 'RELC'
               AND col = 'rate'
               THEN value
           END) AS Relc_Rate
   ,max(CASE 
           WHEN Servicetype = 'RELC'
               AND col = 'consumption'
               THEN value
           END) AS Relc_Consumption
   ,max(CASE 
           WHEN Servicetype = 'RELC'
               AND col = 'daysofservice'
               THEN value
           END) AS Relc_DaysOfService
   ,max(CASE 
           WHEN Servicetype = 'RELC'
               AND col = 'unitofmeasure'
               THEN value
           END) AS Relc_UnitOfMeasure
   ,max(CASE 
           WHEN Servicetype = 'RELC'
               AND col = 'chargeamount'
               THEN value
           END) AS Relc_Charge
   ,max(CASE 
           WHEN Servicetype = 'RELC'
               AND col = 'chargedate'
               THEN value
           END) AS Relc_ChargeDate
--RGAS columns
   ,max(CASE 
           WHEN Servicetype = 'RGAS'
               AND col = 'rate'
               THEN value
           END) AS Rgas_Rate
   ,max(CASE 
           WHEN Servicetype = 'RGAS'
               AND col = 'consumption'
               THEN value
           END) AS Rgas_Consumption
   ,max(CASE 
           WHEN Servicetype = 'RGAS'
               AND col = 'daysofservice'
               THEN value
           END) AS Rgas_DaysOfService
   ,max(CASE 
           WHEN Servicetype = 'RGAS'
               AND col = 'unitofmeasure'
               THEN value
           END) AS Rgas_UnitOfMeasure
   ,max(CASE 
           WHEN Servicetype = 'RGAS'
               AND col = 'chargeamount'
               THEN value
           END) AS Rgas_Charge
   ,max(CASE 
           WHEN Servicetype = 'RGAS'
               AND col = 'chargedate'
               THEN value
           END) AS Rgas_ChargeDate

FROM
--unpivot original data
(
select MyID,customer,Servicetype, col, value
from supercharge
cross apply
(
 select 'rate', cast(servicerate as varchar(10)) union all
 select 'consumption', cast(consumption as varchar(10)) union all
 select 'daysofservice', cast(daysofservice as varchar(10)) union all
 select 'unitofmeasure', cast(unitofmeasure as varchar(10)) union all
 select 'chargeamount', cast(chargeAmount as varchar(10)) union all
 select 'chargeDate', convert(varchar(10), chargeDate,121)
) c(col, value) 
) d
GROUP BY MyID
   ,customer;

---

| MyID  | customer | Relc_Rate | Relc_Consumption | Relc_DaysOfService | Relc_UnitOfMeasure | Relc_Charge | Relc_ChargeDate | Rgas_Rate | Rgas_Consumption | Rgas_DaysOfService | Rgas_UnitOfMeasure | Rgas_Charge | Rgas_ChargeDate |
|-------|----------|-----------|------------------|--------------------|--------------------|-------------|-----------------|-----------|------------------|--------------------|--------------------|-------------|-----------------|
| 10016 | 112992   | 110W      | 753              | 31                 | KWH                | 99.92       | 2019-05-08      | 120       | 0                | 31                 | CCF                | 5.31        | 2019-05-08      |

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