Hi,

I have a table like this

ID    COL1    COL2
----------------------
1        A
1                    X
1                    Y
1        B
1        C



I have a need to output the table with this result:

1    A    X
1    B    Y
1    C

In other words, I want a minimum numer of rows that will have all the data.

I don't care how the COL1 and COL2 paired out, as long as they are within 
the same ID.

Thanks in advance. 

Phil Hunt (aaa@aaa.com) writes:
> I have a table like this
> 
> ID    COL1    COL2
> ----------------------
> 1        A
> 1                    X
> 1                    Y
> 1        B
> 1        C
> 
> 
> 
> I have a need to output the table with this result:
> 
> 1    A    X
> 1    B    Y
> 1    C
> 
> In other words, I want a minimum numer of rows that will have all the
> data. 
> 

WITH col1data AS (
   SELECT ID, COL1, 
          row_number() OVER(PARTITION BY ID ORDER BY COL1) AS rowno
   FROM   tbl
   WHERE  COL1 IS NOT NULL
), col2data AS (
   SELECT ID, COL2, 
          row_number() OVER(PARTITION BY ID ORDER BY COL2) AS rowno
   FROM   tbl
   WHERE  COL2 IS NOT NULL
)
SELECT coalesce(a.ID, b.ID) AS ID, a.COL1, b.COL2
FROM   col1data a
FULL JOIN col2data b ON a.ID = b.ID
                    AND a.rowno = b.rowno

Note that since you did not provide CREATE TABLE statements and INSERT
statements with sample data, I did not care to produce that myself, 
and this is thus an untested solution.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Thanks. It worked.



"Erland Sommarskog"  wrote in message 
news:XnsA21DE77F52069Yazorman@127.0.0.1...
> Phil Hunt (aaa@aaa.com) writes:
>> I have a table like this
>>
>> ID    COL1    COL2
>> ----------------------
>> 1        A
>> 1                    X
>> 1                    Y
>> 1        B
>> 1        C
>>
>>
>>
>> I have a need to output the table with this result:
>>
>> 1    A    X
>> 1    B    Y
>> 1    C
>>
>> In other words, I want a minimum numer of rows that will have all the
>> data.
>>
>
> WITH col1data AS (
>   SELECT ID, COL1,
>          row_number() OVER(PARTITION BY ID ORDER BY COL1) AS rowno
>   FROM   tbl
>   WHERE  COL1 IS NOT NULL
> ), col2data AS (
>   SELECT ID, COL2,
>          row_number() OVER(PARTITION BY ID ORDER BY COL2) AS rowno
>   FROM   tbl
>   WHERE  COL2 IS NOT NULL
> )
> SELECT coalesce(a.ID, b.ID) AS ID, a.COL1, b.COL2
> FROM   col1data a
> FULL JOIN col2data b ON a.ID = b.ID
>                    AND a.rowno = b.rowno
>
> Note that since you did not provide CREATE TABLE statements and INSERT
> statements with sample data, I did not care to produce that myself,
> and this is thus an untested solution.
>
>
> -- 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se