CODEWITHSUNDEEP
sqloracle11g
Noman Arain
Noman Arain

Reputation: 1162

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.

Upvotes: 0

Views: 1181

Answers (2)

dh2002
dh2002

Reputation: 31

Try using PIVOT (I think it's only good for Oracle 11g and after though). Also, i'm new to them, but I'm 99% sure it's how you will get everything on one row like you want.

SELECT * FROM ( 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) AS S1 FROM MAP WHERE POSTAL_CODE = '02216' GROUP BY status, postal_code, city_state ) PIVOT ( S1 FOR S1 IN ('WATER', 'COFFEE', 'FILTRATION') )

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions