Should I drop this column or is there a query that can achieve a results table without duplicate columns?
First and foremost only ever name the columns you actually need in the select list.  Never ever use SELECT *.

That said, you may already be in a hole out of which you cannot dig.  You could create a view to return only the active rows and "data" columns:

create table T(c1 int, c2 int, active bit);

create view V as
select c1, c2 from T
where active = true;


If you rename the existing, underlying table and use the table's current name for the new view you shouldn't have to change any SQL in the application (except for references to the active column, of course).

Whether you should have the active column at all depends on the application's needs and your operational constraints.  If you don't need this history for audit/ reporting/ whatever then get rid of the column and DELETE rows when they are no longer useful.  Smaller databases are better databases!

If you must retain old records moving them to an archive table is a good solution.  It keeps the operational table small and access fast (did I mention that small is good?).  You may have foreign key constraints in place; that will complicate matters.  A DELETE from the operational table and an INSERT to the archive table will be more work than a simple UPDATE to the active column and hence slower.  If this is a problem, marking them inactive, then moving a short time later in a batch process could work.