Moving Multiple Rows to a Single Row

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 Received on Wed Apr 03 2002 - 19:24:30 CST