SQL ordered columns with not null values and 'group by'

Question

I have these results:

 COL1  | COL2  | COL3  | COL4  | COL5  | ID  |
-------|-------|-------|-------|-------|-----|
   1   |   0   |   0   |   0   |   0   |  9  |
   0   |   0   |   0   |   0   |   5   |  9  |
   0   |   2   |   0   |   0   |   0   |  9  |
   0   |   0   |   0   |   4   |   0   |  9  |

And I want order values with not null values first with group ID column;

 COL1  | COL2  | COL3  | COL4  | COL5  | ID  |
-------|-------|-------|-------|-------|-----|
   1   |   2   |   4   |   5   |   0   |  9  |

My Query;

select max(COL1), max(COL2), max(COL3), max(COL4), max(COL5), ID FROM MY_TABLE GROUP BY ID

Then query gave me;

 COL1  | COL2  | COL3  | COL4  | COL5  | ID  |
-------|-------|-------|-------|-------|-----|
   1   |   2   |   0   |   4   |   5   |  9  |

EDIT actually my_table is sub-query table. sub-query might be confusing. MY original Table is:

 ID | VALUE | SPEC_ID  
----|-------|--------
 1  |   1   |    9   
 2  |   2   |    9   
 3  |   4   |    9   
 4  |   5   |    9   
 5  |   1   |    8   
 6  |   3   |    8

and I want:

 COL1  | COL2  | COL3  | COL4  | COL5  | ID  |
-------|-------|-------|-------|-------|-----|
   1   |   2   |   4   |   5   |   0   |  9  |
   1   |   3   |   0   |   0   |   0   |  8  |

Why are you discarding zeroes? They are bona fide values. Also, what happens when multiple rows have non-zero values, in COL2 for example? — The Impaler
– The Impaler, Commented Apr 24, 2023 at 12:41
Did you really expect those values? How you want to get 4 in COL3 and 5 in COL4? — SebCza
– SebCza, Commented Apr 24, 2023 at 12:43
Multiple rows cannot have nonzero values in COL2. Only 1 row can have values in each column and these values are fixed. For example, in column 1, only 1 row has a value other than 0, which is 1. — Hasan Kaan TURAN
– Hasan Kaan TURAN, Commented Apr 24, 2023 at 12:43
I gotta say this sounds pretty un-normalized. If each ID can only have a single value at a time, why even bother storing the zeroes? If there's multiplicity involved, this is not the design approach I would take. — Rogue
– Rogue, Commented Apr 24, 2023 at 12:57
actually this is my subquery. My original table; ID - VALUE - MY_ID 1- 1- 9 2- 2- 9 3- 4- 9 4- 5- 9 — Hasan Kaan TURAN
– Hasan Kaan TURAN, Commented Apr 24, 2023 at 13:46

MT0 · Accepted Answer · 2023-04-24 15:26:59Z

In Oracle, you can use the ROW_NUMBER analytic function to order the rows and then PIVOT to pivot them from rows to columns:

SELECT COALESCE(col1, 0) AS col1,
       COALESCE(col2, 0) AS col2,
       COALESCE(col3, 0) AS col3,
       COALESCE(col4, 0) AS col4,
       COALESCE(col5, 0) AS col5,
       spec_id AS id
FROM   (
  SELECT value,
         spec_id,
         ROW_NUMBER() OVER (PARTITION BY spec_id ORDER BY value) AS rn
  FROM   table_name
)
PIVOT (
  MAX(value)
  FOR rn IN (1 AS col1, 2 AS col2, 3 AS col3, 4 AS col4, 5 AS col5)
);

Which, for the sample data:

CREATE TABLE table_name ( ID, VALUE, SPEC_ID) AS
SELECT 1, 1, 9 FROM DUAL UNION ALL   
SELECT 2, 2, 9 FROM DUAL UNION ALL
SELECT 3, 4, 9 FROM DUAL UNION ALL
SELECT 4, 5, 9 FROM DUAL UNION ALL
SELECT 5, 1, 8 FROM DUAL UNION ALL
SELECT 6, 3, 8 FROM DUAL;

Outputs:

COL1	COL2	COL3	COL4	COL5	ID
1	3	0	0	0	8
1	2	4	5	0	9

fiddle

The Impaler · Accepted Answer · 2023-04-24 16:23:37Z

1

You didn't mention the database so I'll assume it's PostgreSQL.

This is definitively non-relational logic, so the query ends up being unnecessarily longer. You can do:

select
  max(case when rn = 1 then c end) as col1,
  max(case when rn = 2 then c end) as col2,
  max(case when rn = 3 then c end) as col3,
  max(case when rn = 4 then c end) as col4,
  max(case when rn = 5 then c end) as col5,
  id
from (
  select x.*, row_number() over(partition by id order by rk) as rn
  from (
    select id, max(col1) as c, 1 as rk from t group by id
    union all select id, max(col2), 2 as rk from t group by id
    union all select id, max(col3), 3 as rk from t group by id
    union all select id, max(col4), 4 as rk from t group by id
    union all select id, max(col5), 5 as rk from t group by id
  ) x
  where c <> 0
) y
group by id

Result:

 col1  col2  col3  col4  col5  id 
 ----- ----- ----- ----- ----- -- 
 1     3     null  null  null  8  
 1     2     4     5     null  9

See running example at db<>fiddle.

EDIT: I updated the solution with grouping by ID.

edited Apr 24, 2023 at 16:23

answered Apr 24, 2023 at 12:58

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

2 Comments

Hasan Kaan TURAN Over a year ago

Not correct. I have a lot of different id your db<>fiddle

The Impaler Over a year ago

@HasanKaanTURAN I updated the solution with grouping by ID.

Collectives™ on Stack Overflow

SQL ordered columns with not null values and 'group by'

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related