June 26, 2003 at 2:13 am
dear folks this is lil bit urgent issue...
i have one table with certain records ....
col1 col2 col3 col4 col5 col6
IT3 njfghj 0 0 0 Blue
IT3 njfghj 0 0 Simple 0
IT3 njfghj 0 35 0 0
IT3 njfghj Lee 0 0 0
i want result as
col1 col2 col3 col4 col5 col6
IT3 njfghj Lee 35 Simple Blue
means eliminating all the zeros and just wanna result in the above format.
how is this posible??
June 26, 2003 at 3:06 am
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
🙂
🙂
June 26, 2003 at 6:10 am
Have you tried:
select max(col1),
max(col2),
max(col3),
max(col4),
max(col5),
max(col6)
from tablename
?
June 26, 2003 at 6:44 am
Or
select col1,
col2,
max(col3),
max(col4),
max(col5),
max(col6)
from tablename
group by col1,col2
to group by first 2 cols.
Edited by - davidburrows on 06/26/2003 06:44:56 AM
Far away is close at hand in the images of elsewhere.
Anon.
June 26, 2003 at 10:19 pm
thanks folks...i have done this with "max" and got the desired results.
thanks again
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply