Summarise a SQL List

[mark06]

I have a table ...

ID TXT
1 dogfood
1 catfood
1 catfood
1 catfood
2 dogfood
3 dogfood
3 <null>
4 <null>
5 dogfood
5 dogfood
5 dogfood

and I want it to produce ...

ID TXT
1 mixed
2 dogfood
3 mixed
4 <null>
5 dogfood

(in other words, if all txt values are the same for an id, return that value; if there is at least one difference, return 'mixed'

I need to do this in SQL, and not in PL/SQL. It's driving me nuts!

Any ideas? Am I missing something obvious?

 

[karluk]

Give the following a try:

SELECT ID,MAX(TXT)
FROM
(SELECT ID, TXT FROM YOUR_TABLE
GROUP BY ID,TXT)
GROUP BY ID
HAVING COUNT(*) = 1

UNION

SELECT ID,'MIXED'
FROM
(SELECT ID, TXT FROM YOUR_TABLE
GROUP BY ID,TXT)
GROUP BY ID
HAVING COUNT(*) > 1

 

[sky1]

You can also do this assumming name of table is food...

select distinct aid, decode(count(*),1,atxt,'mixed')
from (select distinct a.id aid, a.txt atxt, b.txt btxt
from food a, food b
where a.id = b.id)
group by aid, atxt

 

[sem]

select id, decode(max(txt), min(txt), min(txt),'mixed')
from &quot;table&quot; group by id