在Oracle中，您可以使用ROW_NUMBER分析函数对行进行排序，然后使用PIVOT将它们从行透视到列：

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)
);

其中，对于样本数据：

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;

输出：
| COL1|COL2|COL3|COL4|COL5|ID|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 1|三|0|0|0|八个|
| 1|二|四|五|0|九|
fiddle

你没有提到数据库，所以我假设它是PostgreSQL。
这肯定是非关系逻辑，因此查询最终会不必要地变长。您可以：

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

结果：

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

请参见db<>fiddle上的运行示例。

EDIT：我更新了按ID分组的解决方案。