Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: Lennart Jonsson <erik.lennart.jonsson@gmail.com>
Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming
Subject: Re: SQL help
Date: Fri, 30 Sep 2011 21:39:59 +0200
Organization: A noiseless patient Spider
Lines: 38
Message-ID: <j655ug$uv3$1@dont-email.me>
References: <j653m9$84v$1@speranza.aioe.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Injection-Date: Fri, 30 Sep 2011 19:40:00 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="s5bGbN1rkKdRsNtF9wQKGw"; logging-data="31715"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19RjwKaqO9Sh81z8v9hJvFq"
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.22) Gecko/20110906 Fedora/3.1.14-1.fc14 Lightning/1.0b2 Thunderbird/3.1.14
In-Reply-To: <j653m9$84v$1@speranza.aioe.org>
Cancel-Lock: sha1:pRo9YJY9o1UCrbos//VzdBmpb1Y=
X-TagToolbar-Keys: D20110930213959482
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:695

On 2011-09-30 21:01, Phil Hunt wrote:
>   Table2 ID Status
>       789 4
>       789 4
>       1374
> 
>       1374
> 
>       1374
> 
>       1940 15
>       1940 15
>       1940 15
> 
> 
> I want to Select the above to output the followng
> 
> 789    0
> 1374   0
> 1940    1 (because it has Status of 15)
> 
> 
> I have been staring at this so long, my head and eye hurts. Can someone help 
> ?
> 

I assume there is a good reason for the design of this table. Something
like:

select id, max(case when status = 15 then 1 else 0 end)
from t2
group by id

will do.


/Lennart