A (primitive) solution could be joining 4 time the table with itself, like this:

select

tbl3.col1, tbl3.col2,

tbl3.col3, tbl4.col4, tbl5.col5, tbl6.col6

from

(select * from #tmp where col3 is not null) tbl3

inner join

(select * from #tmp where col4 is not null) tbl4

on tbl3.col1 = tbl4.col1 and tbl3.col2 = tbl4.col2

inner join

(select * from #tmp where col5 is not null) tbl5

on tbl3.col1 = tbl5.col1 and tbl3.col2 = tbl5.col2

inner join

(select * from #tmp where col6 is not null) tbl6

on tbl3.col1 = tbl6.col1 and tbl3.col2 = tbl6.col2

In my example the table #tmp contains the values you provided in your example, and I used NULL values instead zero (but the values and conditions can be altered according to your needs). This is the first solution that I could come up with wright now. I am curious to see what other solutions will come.

Best regards,

Boti

🙂