Create Table with Variable included in Table Name

Hi,

I am trying to create a routine for generating tables from a master
response table. The end result would increment the variable
"somevalue" by one and then create the next table (ie: TESTTEST2). Is
there a way to insert the variable into the Table Name as well as one
or more of the Field Names within the table? I've tried some
different syntax variations and haven't hit on the right combo yet!


.....other stuff.....
....
DECLARE @somevalue AS INT
SET @somevalue = 1
frontend:
IF somevalue > 10
BEGIN TRANSACTION
GO
CREATE TABLE TESTTEST+CAST(@somevalue AS char(2))
(Company_Id char(10),
Question_Q+CAST(@somevalue AS char(2)) char(200),
Response_Q char(40),
Short_Answer_Q char(40))
GO
COMMIT
@somevalue = @somevalue+1
GOTO front_end

Thanks for any assistance!

Chris.

Re: Create Table with Variable included in Table Name

Chris H (chollstein@broadreachpartnersinc.com) writes:
> I am trying to create a routine for generating tables from a master
> response table. The end result would increment the variable
> "somevalue" by one and then create the next table (ie: TESTTEST2). Is
> there a way to insert the variable into the Table Name as well as one
> or more of the Field Names within the table? I've tried some
> different syntax variations and haven't hit on the right combo yet!

You can read here how to do to it - and why you probably shouldn't.
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Create Table with Variable included in Table Name

>> The end result would increment the variable "somevalue" by one and then create the next table (ie: TESTTEST2). <<

This design flaw is called attribute splitting. A table models a set
of the same kind of things. The should be in a column
of a single table. That is why you have a Personnel table and not
MalePersonnel and FemnalePersonnel tables (split on gender values).

What you have done is mimic a 1950's magnetic tape file system in SQL.
Right down to the labeling convention! You made the classic newbie
mistakes of confusing files with tables and columns with fields. If
you learn the right terms, you will find it is easy to avoid a lot of
problems.

Re: Create Table with Variable included in Table Name

What I was looking for is the correct syntax for performing a similar
operation (actually for a view). Some more detail - I have three
tables: Questions, Responses and Company Question/Response (not the
actual names).

"Questions" stores a question_id and the question text
"Responses" stores a response_id and the response text
"Company Question/Response" stores a company_id, question_id and
response_id (as well as a short text answer)

The "Company Question/Response" table stores multiple lines for
individual companies, each with different question/response ids.

I would like to create a view that would "flatten" all the question/
responses to a single line item for each company. I can do this once
for a database, but we are working with 30+ databases (with different
questions/responses) and I want to generate a script that I could run
for each db that would produce "n" views (where n is the number of
questions for the given db). These individual views would then be
joined to create the master "Company/Question1/Response1/Question2/
Response2/.../QuestionN/ResponseN view for that db. I'm hoping that
with the master view I would be able to join to that view (once) and
be able to display all the columns on output. If so, I can use the
same join across all the dbs without having to customize (the join)
based on the number of questions (obviously, the number of columns and
the header text for each in the reports would have to be changed).

The current joins look something like this:
LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier1 ON
qualifier1.company_id = oncd_company.company_id AND
(qualifier1.company_qualifier_question_id = 'Q01' OR
qualifier1.company_qualifier_question_response_id IS NULL)
LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses1
ON qualifier_responses1.quest_resp_id =
qualifier1.company_qualifier_response_id
LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier2 ON
qualifier2.company_id = oncd_company.company_id AND
(qualifier2.company_qualifier_question_id = 'Q02' OR
qualifier2.company_qualifier_question_response_id IS NULL)
LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses2
ON qualifier_responses2.quest_resp_id =
qualifier2.company_qualifier_response_id
LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier3 ON
qualifier3.company_id = oncd_company.company_id AND
(qualifier3.company_qualifier_question_id = 'Q03' OR
qualifier3.company_qualifier_question_response_id IS NULL)
LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses3
ON qualifier_responses3.quest_resp_id =
qualifier3.company_qualifier_response_id .... this goes on to the
last question.

I think with the master view, I could cut this down to one join (on
all db's):
LEFT OUTER JOIN /*the new master view*/ MV ON MV.company_id =
oncd_company.company_id

Re: Create Table with Variable included in Table Name

>> "Questions" stores a question_id and the question text
"Responses" stores a response_id and the response text
"Company Question/Response" stores a company_id, question_id and
response_id (as well as a short text answer) <<

A response is an attribute of a questionnaire, not a separate thing by
itself. This is an old George Carlin joke about baseball scores
--"now for some baseball scores; 12, 5, 7 and 9"; get the point? IT
cannot exist separated from its entity.

CREATE TABLE Questions
(question_nbr INTEGER NOT NULL PRIMARY KEY,
question_txt VARCHAR(1000) NOT NULL);

CREATE TABLE Questionnaires
(company_id CHAR(9) NOT NULL
REFERENCES Companies(company_id),
question_nbr INTEGER NOT NULL
REFERENCES Questions(question_nbr),
PRIMARY KEY ((company_id, question_nbr),
response_txt VARCHAR(1000) DEFAULT '{{Not Answered}} NOT NULL);

You can now re-use the questions in various questionnaires; if you ask
the same company more than once, then add a survey_nbr to the
Questionnaires table.

>> I would like to create a view that would "flatten" all the question/
responses [Questionnaires] to a single line item for each company. <<

Read about First Normal Form and Tiered Architectures in any RDBMS
book. Formatting is done in the front end and not the database. You
are confusing SQL with a monolithic procedure & file system language
like COBOL.

Re: Create Table with Variable included in Table Name

Chris H (chollstein@broadreachpartnersinc.com) writes:
> The current joins look something like this:
> LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier1 ON
> qualifier1.company_id = oncd_company.company_id AND
> (qualifier1.company_qualifier_question_id = 'Q01' OR
> qualifier1.company_qualifier_question_response_id IS NULL)
> LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses1
> ON qualifier_responses1.quest_resp_id =
> qualifier1.company_qualifier_response_id
> LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier2 ON
> qualifier2.company_id = oncd_company.company_id AND
> (qualifier2.company_qualifier_question_id = 'Q02' OR
> qualifier2.company_qualifier_question_response_id IS NULL)
> LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses2
> ON qualifier_responses2.quest_resp_id =
> qualifier2.company_qualifier_response_id
> LEFT OUTER JOIN cstd_company_qualifier_question_response qualifier3 ON
> qualifier3.company_id = oncd_company.company_id AND
> (qualifier3.company_qualifier_question_id = 'Q03' OR
> qualifier3.company_qualifier_question_response_id IS NULL)
> LEFT OUTER JOIN csta_company_qualifier_responses qualifier_responses3
> ON qualifier_responses3.quest_resp_id =
> qualifier3.company_qualifier_response_id .... this goes on to the
> last question.

Rather than doing a bunch of left-joins, you could pivot your
table as demonstrated by this example:

SELECT OrderID,
Emp1 = MIN(CASE EmployeeID WHEN 1 THEN 'X' END),
Emp2 = MIN(CASE EmployeeID WHEN 2 THEN 'X' END),
Emp3 = MIN(CASE EmployeeID WHEN 3 THEN 'X' END),
Emp4 = MIN(CASE EmployeeID WHEN 4 THEN 'X' END),
Emp5 = MIN(CASE EmployeeID WHEN 5 THEN 'X' END),
Emp6 = MIN(CASE EmployeeID WHEN 6 THEN 'X' END),
Emp7 = MIN(CASE EmployeeID WHEN 7 THEN 'X' END),
Emp8 = MIN(CASE EmployeeID WHEN 8 THEN 'X' END),
Emp9 = MIN(CASE EmployeeID WHEN 9 THEN 'X' END)
FROM Orders
WHERE OrderDate BETWEEN '19970101' AND '19970131'
GROUP BY OrderID
ORDER BY OrderID

In SQL 2005, you can also use the PIVOT keyword, but I don't use it
myself.


As for generating all these views, you may be better doing this from
a traditional language.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx