"Luiz Horacio" <lhoracio@iname.com> wrote in message
news:%23ZY8S1ItHHA.736@TK2MSFTNGP06.phx.gbl...

> Consider:
>
> SQL 2000
> 1) re-casting the string as an xml doc AND
> 2) using OPENXML to render the contents as a table, filtering as
> appropriate
>
> SQL2005
> 1) using a CLR proc to convert the string to a table and joining as
> appropriate OR
> 2) feeding the data as an xml parameter and using the .nodes method to
> render as a table
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Luiz Horacio" <lhoracio@iname.com> wrote in message
> news:%23ZY8S1ItHHA.736@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I have a query where I group results using three conditions: date, exam
> and
> solicitant. Solicitants are selected in WHERE condition using '... where
> solicitant in (string variable) and...'.
>
> My problem is that I have, in string variable, a sequence like
> '70214,70214,58256,58256,0,0,0,0', where each number relates to a specific
> solicitant and '0' to exams where solicitant is not registered.
>
> When I print query result it is fine as long as I have only numbers > 0.
> Since '0' represents solicitants not registered on the system, I should
> not
> group them (I don't know if they are the same person or not, so I can't
> group them).
>
> Is there a way to group only by the 'numbers' greater than '0' or I'll
> have
> to make two separate queries?
>
>
> Thanks for any help,
>
>
> Luiz Horacio
>

On Fri, 22 Jun 2007 02:22:23 -0300, Luiz Horacio wrote:

>Is there a way to group only by the 'numbers' greater than '0' or I'll have to make two separate queries?

> On Fri, 22 Jun 2007 02:22:23 -0300, Luiz Horacio wrote:
>
> (snip)
>>Is there a way to group only by the 'numbers' greater than '0' or I'll
>>have to make two separate queries?
>
> Hi Luiz,
>
> Probably. Using a CASE expression in the GROUP BY is often the answer to
> complex grouping issues.
>
> But I fail to understand the exact requirements you describe. If you
> need more help, then please include CREATE TABLE statements (including
> all constraints, properties, and indexes), INSERT statements (for a few
> well-chosen rows of sample data), and expected output with your next
> post.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

"Luiz Horacio" <lhoracio@iname.com> wrote in message
news:eQ71mYUtHHA.1184@TK2MSFTNGP04.phx.gbl...

> Consider:
>
> SQL 2000
> 1) re-casting the string as an xml doc AND
> 2) using OPENXML to render the contents as a table, filtering as
> appropriate
>
> SQL2005
> 1) using a CLR proc to convert the string to a table and joining as
> appropriate OR
> 2) feeding the data as an xml parameter and using the .nodes method to
> render as a table
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Luiz Horacio" <lhoracio@iname.com> wrote in message
> news:%23ZY8S1ItHHA.736@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I have a query where I group results using three conditions: date, exam
> and
> solicitant. Solicitants are selected in WHERE condition using '... where
> solicitant in (string variable) and...'.
>
> My problem is that I have, in string variable, a sequence like
> '70214,70214,58256,58256,0,0,0,0', where each number relates to a specific
> solicitant and '0' to exams where solicitant is not registered.
>
> When I print query result it is fine as long as I have only numbers > 0.
> Since '0' represents solicitants not registered on the system, I should
> not
> group them (I don't know if they are the same person or not, so I can't
> group them).
>
> Is there a way to group only by the 'numbers' greater than '0' or I'll
> have
> to make two separate queries?
>
>
> Thanks for any help,
>
>
> Luiz Horacio