Given the folowing table:
sessionID | old_col | new_col
1 | Google | Google
1 | null | Google
1 | null | Google
2 | null | Microsoft
2 | Microsoft | Microsoft
2 | null | Microsoft
3 | Google | Google or Microsoft or Yahoo
3 | Microsoft | Google or Microsoft or Yahoo
3 | Yahoo | Google or Microsoft or Yahoo
Is this kind of transformation even possible? I want to
a) GROUP BY Session
b) see all values in that session (most will be null)
c) if there is one that's NOT null, replace all nulls in that group with that non-null value
Hi,
Please check if this works.
SEL * FROM Table1 ORDER BY 1,2;
SessionId | Old_Col |
1 | |
1 | |
1 | |
2 | |
2 | |
2 | Microsoft |
3 | |
3 | Microsoft |
3 | Yahoo |
SessionId | New_Col |
1 | |
1 | |
1 | |
2 | Microsoft |
2 | Microsoft |
2 | Microsoft |
3 | Yahoo |
3 | |
3 | Microsoft |
SELECT t.sessionID,
t.TheCol,
agr.TheCols
FROM TheTable t,
( SELECT sessionID,
XMLAGG(TheCol) (VARCHAR(64)) TheCols
FROM TheTable GROUP BY 1 ) agr
WHERE t.sessionID = agr.sessionID
ORDER BY 1;
HTH
Cheers.
Carlos.