How to combine two values (row) into a single row with custom value?

Question

So I have this sample table. Contains data of what OS are installed of all internet shop branches.

ID   ShopName   PCName   OS
1    Mineski    M101     WinXP
2    Mineski    M102     WinXP
3    GameCity   G201     Win7
4    GameCity   G202     Win7
5    CyberBob   C301     WinXP
6    CyberBob   C302     Win7

I need to query the OS installed by Shop.

I can do this using this query.

select ShopName, OS
from ShopInv
group by ShopName, OS

Expected results would be:

ShopName    OS
CyberBob    Win7
CyberBob    WinXP
GameCity    Win7
Mineski     WinXP

However, I only want 1 row per shop to be listed. So in cases there are more than 1 row (due to different OS version installed) like the sample above. I just want to display Mixed.

So the result would be something like this:

ShopName    OS
CyberBob    Mixed
GameCity    Win7
Mineski     WinXP

Is this feasible on SQL Server 2008?

SQLFiddle

Note: I'm a bit confused on how should I state my question so please do edit it if you like. :)

Answer 1

You can use case with distinct OS value count for each ShopName check :

select ShopName
     , case when count(distinct OS) > 1 then 'Mixed' else min(OS) end
from ShopInv
group by ShopName

SQLFiddle

Answer 2

This is easy: As you want to have one row per shop, group by shop only. Then get the OS with an aggregate function. This can be MIN or MAX. If you detect however, that MIN <> MAX, then you must show 'Mixed' instead.

select 
  ShopName,
  case when MIN(OS) = MAX(OS) then MIN(OS) else 'Mixed' end as OS
from ShopInv
group by ShopName;

Answer 3

As Mentioned in question i have given both the outputs for the expected

 declare @t table (Id int,Shop varchar(10),PCname varchar(10),OS Varchar(10))

    insert into @t (Id,Shop,PCname,os)values (1,'Mineski','M101','WinXP'),
    (2,'Mineski','M102','WinXP'),(3,'GameCity','G201','Win7'),
    (4,'GameCity','G202','Win7'),(5,'CyberBob','C301','WinXP'),
    (6,'CyberBob','C302','Win7')

First result

;with cte as (
select shop,OS,ROW_NUMBER()OVER(PARTITION BY shop,OS ORDER BY shop ) rn from @t)
select shop,OS from cte
where rn = 1

And final result set

;with cte as (
select shop,OS,ROW_NUMBER()OVER(PARTITION BY shop,OS ORDER BY shop ) rn from @t)
,CTE2 AS (
Select shop,CASE WHEN R = 1 THEN 'MIXED' ELSE OS END AS 'OS' from (
select shop,OS,count(rn)R from cte
group by Shop,OS )S )
select DISTINCT shop,OS from CTE2