Need help with this SQL

[Phil Hunt]

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.

[Erland Sommarskog]

Post by Phil Hunt
I have a table like this
ID COL1 COL2
----------------------
1 A
1 X
1 Y
1 B
1 C
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.

[Phil Hunt]

Thanks. It worked.

Post by Erland Sommarskog

Post by Phil Hunt
I have a table like this
ID COL1 COL2
----------------------
1 A
1 X
1 Y
1 B
1 C
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.
--