Summarize by unique records by values-CodePudding

I have a table

ID1	ID2	col1	col2
A	B	x
A	B		x
A	C
A	C

I want my output to be the below table. Logic is that for each unique combination of ID1 and ID2, I want to have col 1 display "X" if it exists in any of the combinations. If no "X" exists in the entire combination, then I want to display it as blank.

ID1	ID2	col1	col2
A	B	x	x
A	C

How do I do that?

CodePudding user response：

You can use the MAX aggregation function on "col1" and "col2", group over "ID1" and "ID2". In this way you'll remove the NULL values for each combination of ids, where there's at least a non-null value.

SELECT ID1,
       ID2,
       MAX(col1) AS col1,
       MAX(col2) AS col2
FROM tab
GROUP BY ID1,
         ID2