1

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');
  • 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. – Scott Hodgin Jun 20 at 19:18
  • @ScottHodgin I've added DDL for testing. Thanks for pointing that out – MindXpert Jun 20 at 20:14
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      |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.