Noman Arain Noman Arain - 7 months ago 19
SQL Question

Re-writing the oracle sql query

my current query is

SELECT
STATUS,
POSTAL_CODE,
CITY_STATE,
(case when SERVICE = 1 THEN org_id) end) AS WATER,
(case when SERVICE = 2 THEN org_id) end) AS COFFEE,
(case when SERVICE = 3 THEN org_id) end) AS FILTRATION
FROM MAP
WHERE POSTAL_CODE = 02216


The result that:

Status Zip City_State Water Coffee Filtration

Serviceable 02216 BOSTON,MA. BEL null null
Serviceable 02216 BOSTON,MA. null BDG null
Serviceable 02216 BOSTON,MA. null null BEL


I would like to collapse this result into one row. Is there some elegant select that I could use or should I just use functions like XMLAGG, XMLELEMENT in order to collapse the result into one row? Also, is there a better way to write those case statements? Possibly using the where clause?

Tried a_horse_with_no_name 's suggestion as below:

SELECT STATUS,
POSTAL_CODE,
SERVICE_ITEM_ID,
MAX((case when SERVICE = 1 THEN org_id)) AS WATER,
MAX((case when SERVICE = 2 THEN org_id)) AS COFFEE,
MAX((case when SERVICE = 3 THEN org_id)) AS FILTRATION
FROM MAP
WHERE POSTAL_CODE = '02216'
GROUP BY STATUS, POSTAL_CODE, SERVICE_ITEM_ID;

STATUS POSTAL_CODE SERVICE_ITEM_ID WATER COFFEE FILTRATION
Serviceable 02216 1300 BEL null null
Serviceable 02216 1302 null BDG null
Serviceable 02216 1304 null null BEL


Still the same result.

Answer

Use group by and an aggregate function:

SELECT STATUS, 
       POSTAL_CODE, 
       CITY_STATE,
       max(case when SERVICE = 1 THEN org_id end) AS WATER,
       max(case when SERVICE = 2 THEN org_id end) AS COFFEE,
       max(case when SERVICE = 3 THEN org_id end) AS FILTRATION
FROM MAP
WHERE POSTAL_CODE = '02216'
GROUP BY status, postal_code, city_state

As aggregates ignore NULL values, this gets reduced into a single row.


I assume postal_code is a varchar column, therefor you should also compare it to a string/varchar value. 02216 is a number (2216), whereas '02216' is a string.