Return to Question

added 1078 characters in body

Source Link

edited Jun 20, 2019 at 20:13

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

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

Source Link

asked Jun 20, 2019 at 19:01

MindXpert

Using PIVOT to convert multiple rows and columns

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