Using PIVOT to convert multiple rows and columns

Question

So my current query output is as follows:

I would like to consolidate all of these rows to just one long row like so:

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

and Pretty much repeat that for RGAS, RWTR, etc all across one row. Yes I understand that will be about over 50 columns if I consolidate all the service types into its own column.

Now I was able to get started with getting the service number into the "Type" using this Pivot query. However, I got stuck after that trying to figure out how to get each service type, along with the rate, etc into it's own column.

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

Here's the DDL for testing:

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');

Answer 1

Well, this solution is not particularly sophisticated (it doesn't use PIVOT), but it would solve your problem. There are probably smarter ways to address the problem, but here is one method.

The solution selects from a derived unpivoted table (note comment about unpivot original data). It groups by MyId and customer and uses CASE expressions to derive the individual column names and values. My example only show two of the servicetype columns broken out into separate columns, but hopefully you'll see the pattern. (I'm not sure how you handle the rows with blank 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      |