Case statement - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Case statement

Author

Topic

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-01-22 : 18:01:00

Here's my select statement - I know I don't have the syntax right, which is where I need help!

Select distinct count(siteName),
Case when 1 then siteName
else 'Many'
end as projSite
from PJPENT where project='133503'

This project has two distinct sites, so ideally, if there are more than one distinct site, I want the result to say "Many", and if there is only one site, then use that site.

This is part of a stored proc, in which the project will be a parameter entered by the user.
Thanks in advance!

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-22 : 18:22:27

[code]SELECT CASE
WHEN recordCount = 1
THEN siteName
ELSE 'Many'
END
FROM (
SELECT COUNT(*) AS recordCount, siteName
FROM PJPENT
WHERE project = '133503'
GROUP BY siteName ) a[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-22 : 18:26:17

or:

select case when count(distinct siteName) = 1 then min(siteName) else 'many' end as [projSite]
from PJPent
where prject = 1

Be One with the Optimizer
TG

bmahony993
Yak Posting Veteran

58 Posts

Posted - 2008-01-22 : 18:33:44

Brilliant - that works like a charm!
Thank you so much :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-22 : 18:58:19

SELECT Project, CASE WHEN MIN(SiteName) < MAX(SiteName) THEN 'Many' ELSE MIN(SiteName) END
FROM PjPent
GROUP BY Project
ORDER BY Project

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources