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:

My goal is to have the results show like this:

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!

CodePudding user response：

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

CodePudding user response：

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