CODEWITHSUNDEEP
oracle-database
JF_needs_help
JF_needs_help

Reputation: 1

How do I Pivot in Oracle query when the value is a comma separated string?

I have rows in a database as follows with a value being a comma separated string of numbers...

starttime   endtime   parametername   parametervalue
---------   -------   -------------   --------------
2:00pm      2:15pm    thisparameter   0,0,111,222,333

The desired result is...

starttime   endtime  thisparameter
---------   -------  -------------
2:00pm      2:15pm   0,0,111,222,333

If this was a number, I could just do a PIVOT on the sum, but that of course doesn't work, because it cannot aggregate the comma separated field.

Any ideas? Thanks!

Upvotes: 0

Views: 762

Answers (2)

MT0
MT0

Reputation: 168588

Use LISTAGG to aggregate within the PIVOT:

SELECT *
FROM   table_name
PIVOT( 
  LISTAGG( parametervalue ) WITHIN GROUP ( ORDER BY parametervalue )
  FOR parametername IN (
    'thisparameter' AS thisparameter
  )
)

Which, for the sample data:

CREATE TABLE table_name ( starttime, endtime, parametername, parametervalue ) AS
SELECT '2:00pm', '2:15pm', 'thisparameter', '0,0,111,222,333' FROM DUAL

Outputs:

STARTTIME | ENDTIME | THISPARAMETER  
:-------- | :------ | :--------------
2:00pm    | 2:15pm  | 0,0,111,222,333

db<>fiddle here

Upvotes: 0

EJ Egyed
EJ Egyed

Reputation: 6094

You can still summarize the comma separated numbers using PIVOT if you split the comma separated numbers into multiple rows.

The query below splits the comma separated numbers into multiple rows, then summarizes them with PIVOT

NOTE: The query below will not work for numbers that have a decimal point. If your numbers do have decimals, you will need to adjust the regex that is splitting the rows.

WITH
    d
    AS
        (SELECT '2:00pm'              AS starttime,
                '2:15pm'              AS endtime,
                'thisparameter'       AS parametername,
                '0,0,111,222,333'     AS parametervalue
           FROM DUAL
         UNION ALL
         SELECT '3:00pm'            AS starttime,
                '3:15pm'            AS endtime,
                'thisparameter'     AS parametername,
                '55,66'             AS parametervalue
           FROM DUAL)
SELECT starttime, endtime, thisparameter
  FROM (SELECT *
          FROM d
               CROSS APPLY (    SELECT REGEXP_SUBSTR (parametervalue,
                                                      '\d+',
                                                      1,
                                                      LEVEL)    AS split_num
                                  FROM DUAL
                            CONNECT BY LEVEL <= REGEXP_COUNT (parametervalue, '\d+')))
       PIVOT (SUM (split_num) FOR parametername IN ('thisparameter' AS thisparameter));


   STARTTIME    ENDTIME    THISPARAMETER
____________ __________ ________________
2:00pm       2:15pm                  666
3:00pm       3:15pm                  121

Upvotes: 1

Related Questions