CODEWITHSUNDEEP
sqlsql-server
user2856527
user2856527

Reputation: 13

Select From Table with Multiple Rows for some ID's, with an identifier value

I currently have the following results:

ID    Location
1     NYC
1     LA
2     NYC
3     PIT

and I'd like the following:

ID    Location
1     Multiple
2     NYC
3     PIT

Does anyone know an easy way to accomplish this?

Upvotes: 1

Views: 62

Answers (3)

Anup Shah
Anup Shah

Reputation: 1254

select ID,case when MIN(name) = MAX(name) then  MIN(name) else 'multiple' end as Location
from <your tabel name>
group by id

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13334

With sub-query, COUNT, CASE, GROUP BY: 

SELECT mm.id, CASE WHEN mm.count > 1 THEN 'Multiple' ELSE loc END As Location
FROM
(SELECT COUNT(id) As count, id, MAX(location) As loc
FROM myTable
GROUP BY id) As mm;

SQL Fiddle

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Here's one way to do it using case with count and distinct:

select distinct id, 
  case when count(location) over (partition by id ) > 1 
    then 'Multiple' else location end Location
from yourtable

Upvotes: 1

Related Questions