I agree with Andy. What you have now is what I call the 'spreadsheet approach' to data modeling where everything is in one wide table. You said one column can mean different things based on the value of another column in the row (you called it a "project"), i.e. a column's value can represent an entity from a different domain than a value in the same column from another row. That approach violates first normal form.
I would also decompose the wide, multi-purpose table into many tables that represent the domains of data you are managing. The approach you have devised to replace the 'spreadsheet approach' is known as an EAV and should only be used in rare, niche cases.
I am interested however on the joining aspects as currently most fields are in one big table (one big table multiple times which means using dynamic sql and passing table names around). Im trying to find a solution that will at least alleviate some of the dynamic sql pain and make the code easier to maintain. I can split the col1 ...col40 into smaller subsets and can put them into mulitple tables. Im still a bit unsure if this is the right approach or not. What this would mean though is i might need to join on one table for a project and not for another. Are such dynamic joins recommended at all ? Any other options ?
What you are struggling with is the impedance mismatch between object-oriented thinking and relational modeling. In short, model your data per the relational model in the database (3NF is a good place to shoot for to begin) and abstract the impedance mismatch in your application layer, i.e. do not impose on the database to handle the abstraction because frankly it is not very good at it and you'll end up devolving your codebase doing backflips with dynamic SQL, self-joins or scalar UDFs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato