How to create dynamic columns?

Hello all,

Does anybody can give me some hints on how to create dynamic columns? long story short, I need to create columns and these columns will be displayed in a grid on a web page (column of a grid control). Base on category or type of report selected some of these columns should get visible or invisible.

I'm thinking of 2 solutions but not sure if there are better ways/optimized method to do so.

Thank you,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
And your solutions are? And your data looks like what? How does your DAL looks like?
A crosstab is the only query that will create dynamic columns.  If you need something else, sharpen your coding pencil because you'll be writing VBA to create the dynamic SQL strings.
Jeffrey CoachmanMIS LiasonCommented:
Yes, please clarify exactly what you mean by "dynamic columns", ...with a specific example.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
> I'm thinking of 2 solutions but not sure if there are better ways/optimized method to do so.

So the task is to read your mind and make a qualified guess. Love that.

I think I better enjoy the weekend spending some time with my lovely wife and a cold beer!

Hah, while rather vague, it looks like this person wants "select a+b as thetotal" in which "a" and "b" are TBD.
You need to put this kind of thing in a stored proc. Research "sql injection" to prevent abuse.
Jeffrey CoachmanMIS LiasonCommented:
Base on category or type of report selected some of these columns should get visible or invisible.
Then this is not really "Dynamic Columns", will probably just involve you using code to select the fields.

Something roughly like this:
Dim strFields As String
Dim strSQLPrefix As String
Dim strSQLSuffix As String
Dim strSQLFull As String

strSQLPrefix = "SELECT"
strSQLSuffix = "FROM YourTable"

If Me.txtCategory = "Sales" Then
    strFields = "FirstName, LastName, City, State, Zip"
ElseIf Me.txtCategory = "Engineering" Then
    strFields = "EmpID, LastName, Department, Salary"
ElseIf Me.txtCategory = "QA" Then
    strFields = "LastName, FirstName, EmpID, Phone"
End If

strSQLFull = strSQLPrefix & " " & strFields & " " & strSQLSuffix

Open in new window

Then you can send strSQLFull to your grid...

akohanAuthor Commented:
Sorry for the lack of information in my question. currently, I am getting different information from client and still waiting for more so I'm getting mixed signals but let me rephrase it:

client is using 5 different worksheets of Excel for 5 different categories e.g.  Sales, manufacturing and etc. They enter information such as part number and descriptions, model and material in each column of those sheets. they keep creating new sheets over and over such that the size of worksheet has grown numerously, such that some of Excel features are not functioning any more! such filtering.

Let's say sales sheet has following columns
bf-number, drawing-no, description, reference, assigned by, assigned date

manufacture has:
part number, material-used-in-part, description, sales-drawing-no, assigned by, assigned date

5 different sheets with different columns in each BUT as you see some of the columns are the same. In fact, according to our last meeting, they want me to create one table for all the columns and make columns visible or invisible from a control panel. They want to be able to include some of the columns in few grids/UI and exclude some of them on other grids base on their needs.

Now, I guess I have to rephrase my question:

What is the proper way of storing columns (above e.g. drawing_no, assigned_date) in on table (Columns) and being able to use them as column names for another table on UI (let's say in a grid).

again, I need to store the columns in a table such as "Columns" where will have visible attribute (boolean) next to it  (to be visible or invisible in run-time).

I think, my problem is that how I can tie the columns data with the other table which will be holding the information?

I hope this is clear, if not please let me know. again I apologize for being not clear in my previous question.

ste5anSenior DeveloperCommented:
Access and SQL Server are relational databases, not Excel. Thus you should model this accordingly to the used entities they use.

A generic approach like you are trying to point out, is often not optimal. Even if the client wants it.

The normal model you would use, when it's really necessary is the EAV model. But this is only used for sparse tables.  Otherwise you'll get serious performance troubles, which are really hard to handle. Cause of the wrongly used model.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
akohanAuthor Commented:
Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.