I have created a database that has a JOBS table, each row of the table has corresponding records in a JOB_TASKS table. Each job may have a different number of records in the JOB_TASKS table.
I am trying to create a query that returns a few fields of the JOBS table, then searches the JOBS_TASKS table for all related records and uses those records to create additional column names back in the query and fill in the data in the correct columns.
So if the JOBS table fields & data look like this:
JOB_ID, NAME, DATE
5000 Bob 2006-01-21
And the JOBS_TASKS table fields & data look like this:
JOB_TASK_ID, JOB_ID, TYPE, COMPLETED
1 5000 100 Yes
2 5000 200 No
Then the query would output:
JOB_ID, NAME, DATE, JOB_TASK_TYPE_100, JOB_TASK_TYPE_200
1 Bob 2006-01-21 Yes No
Note the JOB_TASK_TYPE_100 & JOB_TASK_TYPE_200 fields above are to be created dynamically depending on the number of related records in JOB_TASKS and labeled using the TYPE field.
The idea is to show all the jobs in a GridView and the progress of each job represented accross in columns.
Can you help
Thanks
Matthew
Dynamically creating result columns & data
Jim Vinsel
I haven't manipulated datasets programmatically before so this will be interesting.
I will give it a go and post back here soon. If you have any examples of using datasets it would be appreciated.
Thank you.
Matthew
Meylum
Thanks for your post. I will try using a datareader & dataset to collate the data and then post back here.
Matthew
Sat007
Hi Matthew,
You problem has two solutions. One is to dynamicly create a SQL statement, depending on the number of rows in the JOBS_TASKS table. The first thing you will need to do is a SELECT statement to determine how many task types you have, and then build the same number of columns.
The second solution is the following:
1. Run the following SQL script in your code:
SELECT * FROM JOBS job
JOIN JOBS_TASKS task ON job.JOB_ID = task.JOB_ID
2. Get the result as a SqlReader and start reading the records;
3. Starting building a DataSet;
3. Depending on the results from every read operation on the reader, decide if you are going to add a new row or a new column to the dataset. One row will contain one task, while the columns will contain corresponding the type and the status.
4. Bind the dataset;
From the performance perspective, the second solution will be better. It will also give you more control on how you show the results in the application. Later on you might decide to show the results in a different format and the only change you need will do will be in the presentation part.
Let me know if you have more questions.
Best,
- Nikola.
billlocke
Hi,
I've read you question, and I wish to contribute in solving your problem.But,first I would like to ask if you can get the result you are presenting using the SQL statment. If so I can see no problem. Getting your result using a DataAdapter and filling it to a DataTable, and setting the datatsource of your GridView to your filled datatable, with the GridView's Autogeneratecolumn set to true would eventually show your result into your grid.
Hope I have helped you...if not feel free to respond. I am willing to hear a comment comment from you....thanks.....