Using PIVOT to convert multiple rows and columns

Question

So my current query output is as follows:

enter image description here 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');

• Scott Hodgin - Retired: It would be helpful if you could replace the image of your results with table definition DDL and sample data in the form of actual insert statements. That will keep us from having to type all of the data up for testing.
• MindXpert: @ScottHodgin I've added DDL for testing. Thanks for pointing that out

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日 |