Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
maevr
Posting Yak Master
169 Posts |
Posted - 2013-02-27 : 03:55:22
|
I need help writing a query that set a value to Yes when the value is 'priv'. The table can contain multiple rows with the same fnr, but when the fnr contains 'priv' the value should be set to 'Yes'When at least one fnr contains 'priv' in column pnr then a returning value of 'Yes' should be displayed otherwise 'No'Example:create table #source( fnr nvarchar(10) ,pnr nvarchar(10) null)insert #source(fnr, pnr) values('11111','priv')insert #source(fnr, pnr) values('11111','off')insert #source(fnr, pnr) values('22222','priv')insert #source(fnr, pnr) values('33333','priv')insert #source(fnr, pnr) values('44444','off')insert #source(fnr, pnr) values('55555','off')insert #source(fnr, pnr) values('55555','priv')Expected output:fnr newColumn11111 Yes22222 Yes33333 Yes44444 No55555 Yes |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-27 : 04:00:55
|
[code]SELECT fnrWHEN MAX(CASE WHEN pnr='priv' THEN 1 ELSE 0 END)=1 THEN 'Yes' ELSE 'No' ENDFROM tableGROUP BY fnr[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Ranjit.ileni
Posting Yak Master
183 Posts |
Posted - 2013-02-28 : 07:18:25
|
added missed CASE clausequote: Originally posted by visakh16
SELECT fnr,CASE WHEN MAX(CASE WHEN pnr='priv' THEN 1 ELSE 0 END)=1 THEN 'Yes' ELSE 'No' ENDFROM tableGROUP BY fnr ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
--Irk |
|
|
|
|
|
|
|