Fill Missing Rows With Data

General

Fill Missing Rows With Data

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

2 REPLIES

Re: Fill Missing Rows With Data

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


Re: Fill Missing Rows With Data

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.