Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #691 > unrolled thread
| Started by | "Phil Hunt" <aaa@aaa.com> |
|---|---|
| First post | 2011-09-30 15:01 -0400 |
| Last post | 2011-09-30 15:43 -0400 |
| Articles | 4 — 3 participants |
Back to article view | Back to comp.databases.ms-sqlserver
SQL help "Phil Hunt" <aaa@aaa.com> - 2011-09-30 15:01 -0400
Re: SQL help Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> - 2011-09-30 21:34 +0200
Re: SQL help Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2011-09-30 21:39 +0200
Re: SQL help "Phil Hunt" <aaa@aaa.com> - 2011-09-30 15:43 -0400
| From | "Phil Hunt" <aaa@aaa.com> |
|---|---|
| Date | 2011-09-30 15:01 -0400 |
| Subject | SQL help |
| Message-ID | <j653m9$84v$1@speranza.aioe.org> |
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
[toc] | [next] | [standalone]
| From | Hugo Kornelis <hugo@perFact.REMOVETHIS.info.INVALID> |
|---|---|
| Date | 2011-09-30 21:34 +0200 |
| Message-ID | <4d6c87pa8kbipqr79p0armgacq76558v1k@4ax.com> |
| In reply to | #691 |
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
[toc] | [prev] | [next] | [standalone]
| From | Lennart Jonsson <erik.lennart.jonsson@gmail.com> |
|---|---|
| Date | 2011-09-30 21:39 +0200 |
| Message-ID | <j655ug$uv3$1@dont-email.me> |
| In reply to | #691 |
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
[toc] | [prev] | [next] | [standalone]
| From | "Phil Hunt" <aaa@aaa.com> |
|---|---|
| Date | 2011-09-30 15:43 -0400 |
| Message-ID | <j65653$edc$1@speranza.aioe.org> |
| In reply to | #695 |
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 >
[toc] | [prev] | [standalone]
Back to top | Article view | comp.databases.ms-sqlserver
csiph-web