Is there a way to Pivot Oracle SQL results to show 1 unique ID and the non null values in each column?

user_18496025 0
Viewed 44

I am an Oracle SQL beginner and I have an issue with the code below:

SELECT unique_id,
       CASE
         WHEN type LIKE 'E-%' THEN
          'electric'
         ELSE
          null
       END electric_flag,
       CASE
         WHEN type LIKE 'G-%' THEN
          'gas'
         ELSE
          null
       END gas_flag,
       CASE
         WHEN type LIKE 'W-%' THEN
          'water'
         ELSE
          null
       END water_flag,
       CASE
         WHEN type LIKE 'S-%' THEN
          'wastewater'
         ELSE
          null
       END wastewater_flag
  FROM (SELECT unique_id, type, end_dt 
          FROM table 
         WHERE end_dt IS NULL)

Which gives me the following results:

results

My goal is to have the results show like this:

desired results

It's almost like I want to group the results by the id, ignore rows that are all null, but combine the rows that return with the flag into a single row.

Any help would be greatly appreciated!

pivot oracle sql
2 Answers

Here's a pseudo-code:

SELECT
*
FROM
(
SELECT 
unique_id,
CASE type WHEN LIKE 'E-%' THEN 'electric'
          WHEN LIKE 'G-%' THEN 'gas' 
          WHEN LIKE 'W-%' THEN 'water'
          WHEN LIKE 'S-%' THEN 'wastewater'
          ELSE NULL
END flag
FROM {table}
WHERE end_dt IS NULL
)
PIVOT
(
MAX(flag)
FOR flag IN ('electric' electric_flag, 'gas' gas_flag, 'water' water_flag, 'wastewater' wastewater_flag)
)

Here's an Oracle SQL Fiddle

user_181530560

You're almost there;

  • Conditionals are needed but they should be aggregated
  • a Grouping By unique_id clause should be added during the aggregation
  • a subquery is not needed
  • using ELSE null cases are redundant
  • it's suitable to add a ROW_NUMBER() analytic function in order to generate a column with ordinal values

So, use the following SQL Select Statement of Conditional Aggregation :

SELECT ROW_NUMBER() OVER (ORDER BY unique_id) AS id,
       MAX(CASE
           WHEN type LIKE 'E-%' THEN
            'electric'
         END) AS electric_flag,
       MAX(CASE
           WHEN type LIKE 'G-%' THEN
            'gas'
         END) AS gas_flag,
       MAX(CASE
           WHEN type LIKE 'W-%' THEN
            'water'
         END) AS water_flag,
       MAX(CASE
           WHEN type LIKE 'S-%' THEN
            'wastewater'
         END) AS wastewater_flag
    FROM t 
   WHERE end_dt IS NULL
   GROUP BY unique_id

Demo

user_58413060
Related