please go through the below query. SELECT DR.id , case PC.id when 4 then sum(amount) end , case PC.id when 5 then sum(amount) end , case PC.id when 3 then sum(amount) end , case PC.id when 6 then sum(amount) end , case when pc.id = 2 and DP.id = 211 then sum(amount) end , case when pc.id = 2 and DP.id = 210 then sum(amount) end , case when pc.id = 2 and DP.id = 212 then sum(amount) end , case when pc.id = 2 and DP.id = 213 then sum(amount) end , case when pc.id = 2 and DP.id = 214 then sum(amount) end , case when pc.id = 2 and DP.id = 197 then sum(amount) end , case when PC.id = 1 and DP.id3 = 45 then sum(amount) end FROM table1 DR LEFT JOIN table2 DP ON DP.id1 = DR.id1 LEFT JOIN table3 PL ON DP.id2 = PL.id LEFT JOIN table4 PC ON PL.id1 = PC.id WHERE DR.date = GETDATE() group by DR.id, PC.id, DP.id, DP.id3 hope you understood my requirement. i didn't think this is the right way to do. please help any one can do this in a proper manner. thanks in advance.