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, Apr 24 '23 at 12:41
Did you really expect those values? How you want to get 4 in COL3 and 5 in COL4? — SebCza, Apr 24 '23 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, Apr 24 '23 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, Apr 24 '23 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, Apr 24 '23 at 13:46
Please tag your question with the database you are running: mysql, oracle, postgresql...? — GMB, Apr 24 '23 at 14:48

The Impaler · Answer 1 · 2023-04-24T16:23:37.793

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.

Not correct. I have a lot of different id [your db<>fiddle](https://dbfiddle.uk/ohS-reCL) — Hasan Kaan TURAN, Apr 24 '23 at 14:43

score 1 · Accepted Answer · answered Apr 24 '23 at 15:26

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

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

2 Answers2