Re: Moving Multiple Rows to a Single Row

Time Vest Insurance <reb_at_timevest.com> wrote in message news:<l8zd6xgi7ch.fsf_at_timevest.com>...
> Hi,
>
> I'm trying to solve a particular type of problem that may be related
> to the Pivot_Table methodology mentioned this week in the
> newsgroup. However, it is enough different that I can't see how to
> apply it.
>
> Essentially, I have a long convoluted query that produces results like
> this:
>
>
> ID LVAL CVAL
> -------- -------- -------
> 1 A NULL
> 1 NULL Q
> 2 B NULL
> 2 NULL R
> 3 C NULL
> 4 NULL S
> 5 D NULL
> 5 NULL T
> 6 NULL NULL
> 7 E NULL
> 7 NULL U
>
>
> I want the results to be:
>
>
> ID LVAL CVAL
> -------- -------- -------
> 1 A Q
> 2 B R
> 3 C NULL
> 4 NULL S
> 5 D T
> 6 NULL NULL
> 7 E U
>
>
> In fact, I really want to assign a numeric value and have the results
> be more like:
>
>
> ID LVAL CVAL
> -------- -------- -------
> 1 1 1
> 2 2 2
> 3 3 NULL
> 4 NULL 3
> 5 4 4
> 6 NULL NULL
> 7 5 5
>
>
> DECODE would be really cool for this but I can't use it because the
> query has to work in Sybase and SQL Server as well as Oracle.
>
> Anyone have some brilliant ideas?
>
> Are there any books on Advanced SQL Techniques?
>
>
> Bob

Have you tried using ASCII function?
Something like the following:

select id, max(ascii(lval)), max(ascii(cval)) from your_tbl
group by id;

As I could not decipher the logic in converting the characters to numbers, you can come up with some formula. Received on Tue Apr 23 2002 - 13:04:48 CDT