Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

--https://stackoverflow.com/q/78031456/5298879
create table test(ID int,	Brand text,	Type text);
insert into test values
 (1,'Honda','Coupe')
,(1,'Jeep','SUV')
,(2,'Ford','Sedan')
,(2,'Ford','Crossover')
returning *;
/*
Example for above table:
Rows with ID = 1 have different Brand and Type values, so I want one result row for each column.
For ID = 2 there is only one brand, but multiple types, so only one result row for type.

The desired result would be like this.

ID	Difference
1	Brand
1	Type
2	Type
*/

CREATE TABLE




id
brand
type


1
Honda
Coupe

1
Jeep
SUV

2
Ford
Sedan

2
Ford
Crossover



INSERT 0 4


--OP
SELECT ID, 'Brand' AS Discrepancy
FROM test
GROUP BY ID
HAVING COUNT(DISTINCT Brand) > 1

UNION 

SELECT ID,'Type' AS Discrepancy
FROM test
GROUP BY ID
HAVING COUNT(DISTINCT Type) > 1;


id
discrepancy


1
Brand

2
Type

1
Type



SELECT 3


select id, column_name as "Difference"
from test, json_each_text(to_json(test)) as a(column_name,v)
group by id, column_name
having min(v) <> max(v);


id
Difference


1
brand

2
type

1
type



SELECT 3


--see what it does
select id, column_name, array_agg(distinct v)
from test, json_each_text(to_json(test)) as a(column_name,v)
group by id,column_name;


id
column_name
array_agg


1
brand
{Honda,Jeep}

1
id
{1}

1
type
{Coupe,SUV}

2
brand
{Ford}

2
id
{2}

2
type
{Crossover,Sedan}



SELECT 6

id	column_name	array_agg
1	brand	{Honda,Jeep}
1	id	{1}
1	type	{Coupe,SUV}
2	brand	{Ford}
2	id	{2}
2	type	{Crossover,Sedan}