Creating a Dynamic Table

[jalbao]

I am in a situation where i need to create a temporary table that gets its data from a sql SELECT statement.

Example:

I link two tables together to get a record set;

SELECT TD.TestData,TD.dateAdded,T.TestID,T.TestName
FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID = TD.TestID

The above query will return something like the following:
TestID | TestName | TestData | dateAdded
1 Math 80 2/12/2001
2 Science 93 2/15/2001
1 Math 89 3/1/2001
1 Math 91 3/12/2001
2 Science 78 2/22/2001
3 English 68 3/10/2001

I now want to create a new table that will get it's values from the above record set. I want the newly created table to look something like this:

dateAdded | Math | Science | English
2/12/2001 80 NULL NULL
2/15/2001 NULL 93 NULL
2/22/2001 NULL 78 NULL
3/1/2001 89 NULL NULL
3/10/2001 68 NULL NULL
3/12/2001 91 NULL NULL

Notice that the field names of the newly created table were derived from the initial SELECT statement.

So how do I create a table that loops through a record set to generate the field names and places the data (testData and dateAdded in this case) in the correct columns?

 

[sdh]

The easiest method is to use the into statement in your sql query it should force a new table

SELECT TD.TestData,TD.dateAdded,T.TestID,T.TestName
INTO NEWTABLE
FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID = TD.TestID

remember when you are finished with that table to delete it.

 

[MichaelRed]

The 'tables' do NOT agree

The above query will return something like the following:
TestID | TestName | TestData | dateAdded
1        Math       80         2/12/2001
2        Science    93         2/15/2001
1        Math       89         3/1/2001
1        Math       91         3/12/2001
2        Science    78         2/22/2001
3        English    68         3/10/2001

I now want to create a new table that will get it's values from the above record set. I want the newly created table to look something like this:

dateAdded | Math | Science | English
2/12/2001   80     NULL      NULL
2/15/2001   NULL   93        NULL
2/22/2001   NULL   78        NULL
3/1/2001    89     NULL      NULL
3/10/2001   68     NULL      NULL
3/12/2001   91     NULL      NULL


Look at the [b]BOLD[/b] line here.
dateAdded | Math | Science | English
2/12/2001   80     NULL      NULL
2/15/2001   NULL   93        NULL
2/22/2001   NULL   78        NULL
3/1/2001    89     NULL      NULL[b]
3/10/2001   NULL   NULL      68[/b]
3/12/2001   91     NULL      NULL


This could be accomplished by a "Piviot Table".
 
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over