SQL help

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
?

Thanks

Hi Phil,

SELECT DISTINCT ID, CASE WHEN Status = 15 THEN 1 ELSE 0 END
FROM YourTable;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

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

Thanks guys.
It is not the table. I am showing the tuples after a whole bunch of joins.
I should be able to work this into my scheme.


"Lennart Jonsson" <erik.lennart.jonsson@gmail.com> wrote in message
news:j655ug$uv3$1@dont-email.me...
> 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
>