looking to get a query constructed out for oracle sql developer output-CodePudding

Existing table

Output query should render this

CodePudding user response：

Use GROUP BY and take the MAX of each column:

SELECT event,
       MAX(numericval) AS quantity,
       MAX(details) AS status
FROM   table_name
GROUP BY event

Which, for the sample data:

CREATE TABLE table_name (event, elements, numericval, details) AS
SELECT 1, 'quality', 5, NULL FROM DUAL UNION ALL
SELECT 1, 'status', NULL, 'yes' FROM DUAL UNION ALL
SELECT 2, 'quality', 10, NULL FROM DUAL UNION ALL
SELECT 2, 'status', NULL, 'no' FROM DUAL UNION ALL
SELECT 3, 'quality', 15, NULL FROM DUAL UNION ALL
SELECT 3, 'status', NULL, 'yes' FROM DUAL;

Outputs:

EVENT QUANTITY STATUS

1 5 yes

2 10 no

3 15 yes

EVENT	QUANTITY	STATUS
1	5	yes
2	10	no
3	15	yes

For your update, you can use conditional aggregation:

SELECT event,
       MAX(CASE elements WHEN 'quality' THEN numericval END) AS quantity,
       MAX(CASE elements WHEN 'status' THEN details END) AS status,
       MAX(CASE elements WHEN 'Lot' THEN numericval END) AS lot
FROM   table_name
GROUP BY event

Which for the updated data:

CREATE TABLE table_name (event, elements, numericval, details) AS
SELECT 1, 'quality', 5, NULL FROM DUAL UNION ALL
SELECT 1, 'status', NULL, 'yes' FROM DUAL UNION ALL
select 1 , 'Lot',1,null from dual union all
SELECT 2, 'quality', 10, NULL FROM DUAL UNION ALL
SELECT 2, 'status', NULL, 'no' FROM DUAL UNION ALL
select 2 , 'Lot',3,null from dual union all
SELECT 3, 'quality', 15, NULL FROM DUAL UNION ALL
SELECT 3, 'status', NULL, 'yes' FROM DUAL union all
select 3, 'Lot', 4,null from dual;

Outputs:

EVENT QUANTITY STATUS LOT

1 5 yes 1

2 10 no 3

3 15 yes 4

EVENT	QUANTITY	STATUS	LOT
1	5	yes	1
2	10	no	3
3	15	yes	4

db<>fiddle here