Geek Answers Handbook

How to combine two values ​​(string) in one string with a custom value?

So, I have this sample table. Contains information that the OS installed from all branches of the online store.

 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 request the OS installed by Shop.

I can do this using this query.

 select ShopName, OS from ShopInv group by ShopName, OS

Expected results:

 ShopName OS CyberBob Win7 CyberBob WinXP GameCity Win7 Mineski WinXP

However, I want the list to display one line per store. Thus, in cases of more than 1 line (due to the installed OS version), as 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 possible on SQL Server 2008?

SQLFiddle

Note. I am a little confused about how I should state my question, so please edit it if you want. :)

+5
source share
4 answers

You can use case with distinct OS 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

+6
source

This is easy: since you want to have one line in the store, only for the shopping group. Then get an OS with an aggregate function. It can be MIN or MAX. If you find that MIN <> MAX, then you should show "Mixed".

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

As mentioned in the question, I gave both 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 the final set of results

 ;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
+2
source 
 select Shopname, (select case when count(shopname)>1 then 'Mixed' else OS from ShopInv b where a.shopname=b.shopname group by b.shopname) from ShopInv a
0
source

More articles:


All Articles