Merge Multiple row in one row (Self Join)

Hi , I have a data table like below :-

C1        C2                  C3                 C4       C5               C6

FL   All Other Audis    0                    012     16/5/2009     LT
FL   All Other Audis    0                    030     16/5/2009     LT
FL   All Other Audis    UnLimited        0        16/5/2009     RD
FL   NOT ELIGIBLE      0                   12       12/3/2011     LT
FL   NOT ELIGIBLE      UnLimited       0        12/3/2011     RD

I want to see the data like below :-

C1       C2                    C3                C4          C5     

FL   All Other Audis     UnLimited        012     16/5/2009    
FL   All Other Audis     UnLimited        030     16/5/2009    
FL   NOT ELIGIBLE      UnLimited        12       12/3/2011    

Could you please let me know your opinion how to achieve this ?

Thanks

BKK


  • Edited by Kiroriwal 13 hours 39 minutes ago
January 17th, 2014 4:52pm

Select C1,C2, C3,C4,C5 FROM 
(SELECT C1,C2,Max(C3) Over() as C3,C4,C5 FROM yourtable) t
WHERE C4<>'0'


  • Marked as answer by Kiroriwal 12 hours 54 minutes ago
Free Windows Admin Tool Kit Click here and download it now
January 17th, 2014 5:15pm

Thank you so much Jingyang...!!! You are simply Intelligent. :)
January 17th, 2014 5:39pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics