Creating a Dynamic Table 2

[jalbao]

running sqlServer 2k /windows 2K Server

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?

 

[tlbroadbent]

Try this query which creates a cross tab result.

SELECT
DateAdded,
Case TestName
When Math Then TestData
Else Null
End As Math,
Case TestName
When Science Then TestData
Else Null
End As Science,
Case TestName
When English Then TestData
Else Null
End As English,

FROM

(SELECT
TD.TestData,
TD.DateAdded,
T.TestID,
T.TestName

FROM tblTest T LEFT OUTER JOIN tblTestData TD
ON T.TestID = TD.TestID) As A Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[jalbao]

terry - your solution looks like what i need except for one small detail...

i, as the developer of the app, never knows the name of the tests. in other words, in your first SELECT statement you have "Case TestName When Math Then TestData .. etc". Well, don't know if a test named 'math' will ever exist in the table.

i need someway to grab the existing tests in the table and then use those values (what ever they may be) to create the fields in the new table.

although your query won't solve my problem at hand, i did get some useful stuff out of it - i've never use the SQL Case before... good stuff.

thanks Terry

 

[tlbroadbent]

You could write code to dynamically generate the cross-tab query based on the contents of the TestName column. Then you could execute the dynamically generated SQL. If I typed it correctly, the following should work for you.

-----------------------------
set nocount on

/* Declare variables needed */
declare @sql varchar(1024), @nsql nvarchar(2048)

/* Create start of dynamic SELECT statemetn */
SELECT @sql="SELECT DateAdded,"

/* Create the Cross-tab portion of the SELECT list based on data in the TestName column */
SELECT @sql=@sql + "Sum(Case TestName When '" + TestName + "' Then TestData Else Null End) As " + TestName + ","
FROM (SELECT TD.TestData,TD.DateAdded,T.TestID,T.TestName
FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID) As A
GROUP BY TestName

/* Trim the trailing comma and add the remainder of the required SQL */
SELECT @nsql=left(@sql,datalength(@sql)-1) + " FROM (SELECT TD.TestData,TD.DateAdded,T.TestID,T.TestName FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID) As A"

/* Execute the dynamic SQL */
EXEC sp_executesql @nsql Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[jalbao]

wow terry - that's probably the most intense query i have ever seen!

i ran the query and received the following error message:
"ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with ' FROM (SELECT TD.lTestData,TD.dtAdded,T.lTestID,T.sTestName FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.lTestID=TD.lTestI' is too long. Maximum length is 128".

Being that I am not familiar enough w/ sql to figure out what's going on within the query, i can't figure out what exactly the error message is referring to...

 

[tlbroadbent]

My error! Remove the + "," between "TestName" and the FROM clause.

SELECT @sql=@sql + "Sum(Case TestName When '" + TestName + "' Then TestData Else Null End) As " + TestName + ","
FROM (SELECT TD.TestData,TD.DateAdded,T.TestID,T.TestName
FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID) As A
GROUP BY TestName
Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[jalbao]

ok terry i made the necessary change of getting rid of the comma at the end of the SELECT statement. I then ran the query and received the same error message. I took a fine comb through the query and discovered that there is some issue with the second FROM statement; FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID. Check out the red text that is returned in the error message - you will notice that it says, TD.lTestI instead of TD.lTestID - the letter D got cut off;

Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with ' FROM (SELECT TD.lTestData,TD.dtAdded,T.lTestID,T.sTestName FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.lTestID=TD.lTestI' is too long. Maximum length is 128

Messing around with the query a little bit i found that if i add another set of parentesis () around the content of the FROM statement an error message claims the following (note the red text);

[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with ' FROM (SELECT TD.lTestData,TD.dtAdded,T.lTestID,T.sTestName FROM (tblTest T LEFT OUTER JOIN tblTestData TD ON T.lTestID=TD.lTest' is too long. Maximum length is 128.

If you look closely, you'll notice that the letters I and D were cut off. Every time i add a set of parenthesis, a letter will be cut off..?

Any clues as to why this is going on...

 

[jalbao]

oops - terry, you'll notice that i misspelled the names of the fields just a little bit... instead of TestID i wrote lTestID (long integer) and instead of TestName i wrote sTestName (string). its an old habit - sorry if it confused you.

the misspelled fields were not the cause of the error

 

[tlbroadbent]

OK. Must be the late nights! Increase the size of the variables, @sql and @nsql to 4000. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[jalbao]

I changed the size of the vars to 4000 - didn't fix the problem so i changed the size of the vars to 8000, the same error message is still triggered...?

 

[jalbao]

terry - whatdo you suppose is meant by "The identifier that starts with 'sql statement....etc' is too long. Maximum length is 128".

I counted the sql that generates the error and it is 127 characters long (including spaces). My confusion is two part: 1) I don't know what the error message means and 2)It seems as thought the error message is making a wrong statement (in my experience though, that's usually not the case).

 

[tlbroadbent]

Please post the query you are executing. I'm just not finding a problem and have not been able to duplicate the error in my testing. I'm sure I've missed something but can't determine what. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[jalbao]

terry - i simply did a copy and paste of the query that you wrote and made the changes that you suggested... the following is a copy/paste of the query that i am testing in SQL Query Analyzer


set nocount on

/* Declare variables needed */
declare @sql varchar(8000), @nsql nvarchar(8000)

/* Create start of dynamic SELECT statemetn */
SELECT @sql="SELECT DateAdded,"

/* Create the Cross-tab portion of the SELECT list based on data in the TestName column */
SELECT @sql=@sql + "Sum(Case TestName When '" + TestName + "' Then TestData Else Null End) As " + TestName
FROM (SELECT TD.TestData,TD.DateAdded,T.TestID,T.TestName
FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID) As A
GROUP BY TestName

/* Trim the trailing comma and add the remainder of the required SQL */
SELECT @nsql=left(@sql,datalength(@sql)-1) + " FROM (SELECT TD.TestData,TD.DateAdded,T.TestID,T.TestName FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID) As A"

/* Execute the dynamic SQL */
EXEC sp_executesql @nsql

 

[tlbroadbent]

I upgraded to the SQL 2000 client tools, today. After the upgrade I got the same error message. I've now adjusted the query for SQL 2000. Note that I "set quoted_identifier off" at the start of the script. Hope this one works better for you.

set nocount on
set quoted_identifier off

/* Declare variables needed */
declare @sql varchar(4000), @nsql nvarchar(4000)

/* Create start of dynamic SELECT statement */
SELECT @sql="SELECT DateAdded,"

/* Create the Cross-tab portion of the SELECT list based on data in the TestName column */
SELECT @sql=@sql +
' Sum(Case TestName When "' + TestName +
'" Then TestData Else 0 End) As ' + TestName + ','
FROM (SELECT TD.TestData,T.TestName
FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID) As A
GROUP BY TestName

/* Trim the trailing comma and add the remainder of the required SQL */
SELECT @nsql=left(@sql,datalength(@sql)-1) +
' FROM (SELECT TD.TestData,TD.DateAdded,T.TestID,T.TestName' +
' FROM tblTest T LEFT OUTER JOIN tblTestData TD ON T.TestID=TD.TestID) As A' +
' GROUP BY DateAdded'

/* Execute the dynamic SQL */
EXEC sp_executesql @nsql Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[jalbao]

terry - your query works perfect. you have been a great help - thank you!