I want to summarize my output in a single row based on one column-CodePudding

I have a result set like ID product attribute attribute id

id	product	attribute	attributeid
1	x	A	1
1	x	B	2
1	x	C	3

Now I want my final result set as a single column let say in case of attribute id 1 I want to populate specific column and for 2 another and 3 another. So My Final output should be like

id	product	attribute1	attribute2	attribute2
1	x	A	B	C

CodePudding user response：

You can use conditional aggregation while grouping by id, product columns such as

SELECT id, product, 
       MAX(CASE WHEN attributeid = 1 THEN attribute END) AS attribute1,
       MAX(CASE WHEN attributeid = 2 THEN attribute END) AS attribute2,
       MAX(CASE WHEN attributeid = 3 THEN attribute END) AS attribute3
  FROM t
 GROUP BY id, product

CodePudding user response：

This is the type of problem that PIVOT was designed to solve:

SELECT *
FROM   table_name
PIVOT (
 MAX(attribute)
 FOR attributeid IN (1 AS attribute1, 2 AS attribute2, 3 AS attribute3)
)

Which, for your sample data:

CREATE TABLE table_name (id, product, attribute, attributeid) AS
SELECT 1, 'x', 'A', 1 FROM DUAL UNION ALL
SELECT 1, 'x', 'B', 2 FROM DUAL UNION ALL
SELECT 1, 'x', 'C', 3 FROM DUAL

Outputs:

ID PRODUCT ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3

1 x A B C

db<>fiddle here