Fill Missing Rows With Data

[uspowpow]

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

[chinmay_uce02]

Hi,

Please check if this works.

SEL * FROM Table1 ORDER BY 1,2;

SessionId	Old_Col
1
1
1	Google
2
2
2	Microsoft
3	Google
3	Microsoft
3	Yahoo

SEL T1.SessionId, COALESCE(T1.OLd_Col,T2.Old_Col) AS New_Col

FROM Table1 T1 LEFT OUTER JOIN 

(SEL * FROM Table1 WHERE Old_Col IS NOT NULL QUALIFY RANK() OVER(PARTITION BY SessionId ORDER BY Old_Col) = 1) T2 

ON T1.SessionId = T2.SessionId 

ORDER BY 1;

SessionId	New_Col
1	Google
1	Google
1	Google
2	Microsoft
2	Microsoft
2	Microsoft
3	Yahoo
3	Google
3	Microsoft

[CarlosAL]

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.