I want to select the maximum value, but if the user has more than one value in one column, then leave all

I have a table :

create table package (U_id, service, offer,product) as
 select   1, 12345, null, null  from dual union all
select   1, null, 25468, null  from dual union all
select   1, null, null, 23456  from dual union all
select   2, 12345, 58889, null  from dual union all
select   2, 43456, null, null  from dual ;

I tried

select u_id, max(service), max(offer),max(product) 
from package
group by u_id

but, in the case of the second user, took only the maximum number from the service.

I want to get such a table:

CodePudding user response：

From Oracle 12, you can use analytic functions to number the rows and then use conditional aggregation inside a LATERAL self-join to get the values into the correct rows:

WITH row_counts (
  u_id,
  service,
  offer,
  product,
  rn,
  service_rn,
  offer_rn,
  product_rn,
  num_rows
) AS (
  SELECT u_id,
         service,
         offer,
         product,
         ROW_NUMBER() OVER (PARTITION BY u_id ORDER BY ROWNUM) AS rn,
         NVL2(service,
           ROW_NUMBER() OVER (
             PARTITION BY u_id ORDER BY NVL2(service, ROWNUM, NULL) NULLS LAST
           ),
           NULL
         ) AS service_rn,
         NVL2(offer,
           ROW_NUMBER() OVER (
             PARTITION BY u_id ORDER BY NVL2(offer, ROWNUM, NULL) NULLS LAST
           ),
           NULL
         ) AS offer_rn,
         NVL2(product,
           ROW_NUMBER() OVER (
             PARTITION BY u_id ORDER BY NVL2(product, ROWNUM, NULL) NULLS LAST
           ),
           NULL
         ) AS product_rn,
         GREATEST(
           COUNT(service) OVER (PARTITION BY u_id),
           COUNT(offer)   OVER (PARTITION BY u_id),
           COUNT(product) OVER (PARTITION BY u_id)
         ) AS num_rows
  FROM   package
)
SELECT r.u_id,
       d.service,
       d.offer,
       d.product
FROM   row_counts r
       CROSS JOIN LATERAL (
         SELECT MAX(CASE d.service_rn WHEN r.rn THEN d.service END) AS service,
                MAX(CASE d.offer_rn   WHEN r.rn THEN d.offer   END) AS offer,
                MAX(CASE d.product_rn WHEN r.rn THEN d.product END) AS product
         FROM   row_counts d
         WHERE  r.u_id = d.u_id
       ) d
WHERE  r.rn <= r.num_rows
ORDER BY r.u_id, r.rn;

Which, for the sample data:

CREATE TABLE package (U_id, service, offer, product) AS
  SELECT 1, 12345, NULL, NULL FROM DUAL UNION ALL
  SELECT 1, NULL, 25468, NULL FROM DUAL UNION ALL
  SELECT 1, NULL, NULL, 23456 FROM DUAL UNION ALL
  SELECT 2, 12345, 58889, NULL FROM DUAL UNION ALL
  SELECT 2, 43456, NULL, NULL FROM DUAL;

Outputs:

U_ID	SERVICE	OFFER	PRODUCT
1	12345	25468	23456
2	12345	58889	null
2	43456	null	null

db<>fiddle here

CodePudding user response：

This might be one option:

Sample data:

SQL> WITH
  2     package (U_id,
  3              service,
  4              offer,
  5              product)
  6     AS
  7        (SELECT 1, 12345, NULL, NULL FROM DUAL
  8         UNION ALL
  9         SELECT 1, NULL, 25468, NULL FROM DUAL
 10         UNION ALL
 11         SELECT 1, NULL, NULL, 23456 FROM DUAL
 12         UNION ALL
 13         SELECT 2, 12345, 58889, NULL FROM DUAL
 14         UNION ALL
 15         SELECT 2, 43456, NULL, NULL FROM DUAL),

Query begins here: temp CTE calculates number of different service/offer/product values per each u_id; it is then checked against total number of rows per u_id in the group by clause so that query "knows" whether to group by those columns or not

 16     temp
 17     AS
 18        (  SELECT u_id,
 19                  COUNT (*) cnt,
 20                  COUNT (DISTINCT service) cnt_service,
 21                  COUNT (DISTINCT offer) cnt_offer,
 22                  COUNT (DISTINCT product) cnt_product
 23             FROM package
 24         GROUP BY u_id)
 25    SELECT p.u_id,
 26           MAX (p.service) service,
 27           MAX (p.offer) offer,
 28           MAX (p.product) product
 29      FROM package p JOIN temp t ON t.u_id = p.u_id
 30  GROUP BY p.u_id,
 31           CASE
 32              WHEN t.cnt <> t.cnt_service   t.cnt_offer   t.cnt_product
 33              THEN
 34                 p.service || p.offer || p.product
 35              ELSE
 36                 NULL
 37           END
 38  ORDER BY p.u_id;

      U_ID    SERVICE      OFFER    PRODUCT
---------- ---------- ---------- ----------
         1      12345      25468      23456
         2      12345      58889
         2      43456

SQL>