August 31, 2006 at 7:46 pm
I have a table of questions with columns Q1, Q2, Q3 etc through Q46.
In another table, user answers pulled from an online survey are stored in columns A1, A2, A3 etc through A46.
I want to loop through the values 1 ... 46
and then do some various things - number crunching and all - to write a simple report on each question.
The nut I'm trying to crack is that I want to somehow concatenate the value I'm on - somewhere between 1 and 46 - to the letter A and again to the letter Q so that I can then query the columns A4 and Q4 and write my little report, and then move on to A5 and Q5 ...
In Oracle I think I can manage this kind of thing with "execute immediate" but in MS SQL and T-SQL I am at a loss.
Can anyone help?
Thanks!
September 1, 2006 at 9:41 am
Your requirement is not very clear. Given what I can guess about it, looks like you want to use dynamic SQL and the EXEC command.
Here is an example:
SET
NOCOUNT ON
DROP
TABLE Answers
DROP
table Questions
go
CREATE
Table Questions
(
qnId int identity(1,1) primary key,
Q1
varchar(100),
Q2
varchar(100),
Q3
varchar(100),
Q4
varchar(100)
)
CREATE
TABLE Answers
(
ansID int identity(1,1) primary key,
A1
varchar(100),
A2
varchar(100),
A3
varchar(100),
A4
varchar(100),
qnID
int FOREIGN KEY references Questions(qnID)
)
/*
Assumes each answers row
responds to exactly ONE questions row
*/
INSERT
INTO Questions
SELECT
'This is question 1'
,
'This is question 2'
,
'This is question 3'
,
'This is question 4'
INSERT
INTO Answers (A1, A2, A3, A4, qnID)
SELECT
'This is answer 1 for response 1'
,
'This is answer 2 for response 1'
,
'This is answer 3 for response 1'
,
'This is answer 4 for response 1'
,
1
INSERT
INTO Answers
SELECT
'This is answer 1 for response 2'
,
'This is answer 2 for response 2'
,
'This is answer 3 for response 2'
,
'This is answer 4 for response 2'
,
1
SELECT
* FROM Questions
SELECT
* FROM ANSWERs
DECLARE
@sql VARCHAR(2000)
DECLARE
@desiredQuestionNumber VARCHAR(2)
DECLARE
@Q char
set
@Q=char(39)
--create query string for wuestion number 3
SET
@desiredQuestionNumber='3'
SET
@sql = 'SELECT Q'
+ ltrim(rtrim(@desiredQuestionNumber))
+ '+' +@Q+' ' +@Q+ '+'
+ 'A'
+ ltrim(rtrim(@desiredQuestionNumber))
+ '
FROM Answers ans
JOIN Questions qns
ON qns.qnID=ans.qnID'
--display the dynamic SQL
SELECT
@SQL
--execute the SQL
EXEC
(@SQL)
Here is the Result:
Dynamic Query: SELECT Q3+' '+A3 FROM Answers ans JOIN Questions qns ON qns.qnID=ans.qnID
EXEC Results:
This is question 3 This is answer 3 for response 1
This is question 3 This is answer 3 for response 2
September 1, 2006 at 11:31 pm
I want to loop through values 1-46. or maybe 1-66; I may not know ahead of time; it will be sort of like a cursor ...
I want to take that value each time and concatenate it to a letter. And I end up with a column name, which I then use in a select query.
So it I am in loop for value 3, I want to concatenate Q and 3 and then be able to use Q3 as a column name.
select Q3 from myTable
How do I dynamically build a column name from a character Q and an integer 3 and then be able to use it in a query?
pseudo code
for i in 1..55
select
from myTable
next i
and I should get the equivalient of
select Q1 from myTable
print Q1
select Q2 from myTable
print Q2
....
select Q55 from myTable
print Q55
More clear?
September 2, 2006 at 10:39 am
Try this:
SET
NOCOUNT ON
DROP
table Questions
go
CREATE
Table Questions
(
qnId int identity(1,1) primary key,
Q1
varchar(100),
Q2
varchar(100),
Q3
varchar(100),
Q4
varchar(100)
)
INSERT
INTO Questions
SELECT
'This is question 1'
,
'This is question 2'
,
'This is question 3'
,
'This is question 4'
DECLARE
@sql VARCHAR (1000)
DECLARE
@i int
DECLARE
@maxI int
SELECT
@i=0
SELECT
@maxI=4
WHILE
@i < @maxI
BEGIN
SET @i=@i+1
SELECT @sql=
'SELECT Q'
+ ltrim(convert(varchar,@i))
+ ' FROM Questions'
SELECT 'Query '+convert(varchar,@i)+': '
+ @SQL AS Query
EXEC (@sql)
END
September 2, 2006 at 1:38 pm
Beautiful - thank you very much!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply