• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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,
  • 2
  • 2
  • 2
  • +3
1 Solution
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.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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", ...it 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.
akohanAuthor Commented:
Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now