Convert Columnar Data to Row Data
-
Hello,
I have a table in SQL Server 2000 in which each record holds a Student_ID (Student_ID column), and a single digit numeric value for each of 75 question columns. These question columns in the record follow the naming convention of 'Q1', 'Q2', 'Q3'...'Q75'. So a single record might look like this:
StudentID Q1 Q2 Q3 ....
5000232 2 1 2
I need to maniplate this data and present it in such a way (perhaps a View)so that the records would come out like this:
StudentID StudentAnswer Question
5000232 2 Q1
5000232 1 Q2
5000232 2 Q3
I'm thinking that I could use some sort of a Crosstab to do this, and I have begun to explore this. Would that be the right idea, or should I use Grouping to achieve this?
Thanks for your help!
CSDunn
-
Pivot table or Cross tab are what you are after.
-
Actually you want to convert EACH record into 75 !
that is easylly accoplished with a Numbers table that contains numbers from 1 to 75
Select StudentID as Student,
Case When n = 1 THEN Q1
When n = 2 THEN Q2
When n = 3 THEN Q3
...
When n = 75 THEN Q75 as Answer,
'Q' + str(n) as QuestionNumber
From Students CROSS JOIN Numbers
where StudentID ...
(I am assuming the studentID is Unique or PK)
HTH
* Noel -
quote:
Select StudentID as Student,Case When n = 1 THEN Q1
When n = 2 THEN Q2
When n = 3 THEN Q3
...
When n = 75 THEN Q75 as Answer,
'Q' + str(n) as QuestionNumber
From Students CROSS JOIN Numbers
where StudentID ...
Noel,
What fields do I join the the Cross Join? The 'Students' table that I have just has the 'StudentID' field and 75 question fields (Q1-Q75). The 'Numbers' table just has one column called 'QNumber' with 75 records in the table (the numbers 1-75).
Thanks again!
CSDunn
-
sorry I was not very clear. you should cross join students table with the Numbers Table (NO COLUMns are linked!!) the end result is the CARTESIAN product of both tables, but be careful because if you dont limit the students then you will have 75 * NumberOfStudents records! and that could be too big. That's why I included a where clause so that if you specif y one studentID you will get 75 rows you could specify several students paging by studentID also. The idea is to limit the cross join output
HTH
* Noel -
quote:
That's why I included a where clause so that if you specif y one studentID you will get 75 rows you could specify several students paging by studentID also.Thanks, this has been a big help! The following is what I have set up:
**************************
DECLARE @Grade AS Smallint
DECLARE @TestID AS Varchar(10)
SET @Grade = 0
SET @TestID = 'HMKRL3'
SELECT
"Grade"=@Grade,
"TestID"=@TestID,
Permnum,
QID,
CA,
Case When QNumber = 1 Then Q1
When QNumber = 2 Then Q2
When QNumber = 3 Then Q3
.....
WHEN QNumber = 75 THEN Q75
END AS SA,
'Q'+ str(QNumber,2)AS QuestionNumber
FROM tblRCScoreImport CROSS JOIN tblRCScoreImportQNumbers
WHERE permnum = '2000022500'
ORDER BY Permnum, QuestionNumber
***********************************
In the CASE statement, I call the column 'SA', meaning 'student answer'.
The variables will be used to help us identify the grade level of the test,
and the ID of the test.
'QID' is the 'question ID' and comes from tblRCScoreImportQNumbers,
'CA' is the 'correct answer' and also comes from tblRCScoreImportQNumbers
I need two other calculated fields in this query:
One would compare 'SA' to 'CA' and the result would populate a calculated field called 'Valid' with a 1 if they did match, and a zero if not:
***
IF SA=CA
@Valid = 1
ELSE
@Valid = 0
***
The other would determine whether or not 'SA' was NULL, and if it were not, then would again compare 'SA' to 'CA' and the result would populate a calculated field called 'Result' with a 1 if they did match, and a zero if not:
***
IF SA is NULL
@Result = 'B'
ELSE
IF SA=CA
@Result = 'C'
ELSE
@Result = 'I'
***
The 'IF' statements may seem to be a bit redundant, but I need to go with it for now. How do I work with the value of 'SA' if it is not an actual field (with a variable arrangement of some kind?)Can I handle @Valid and @Result the same way I did 'Grade' and 'TestID'?
Thanks again for your help!
CSDunn
-
you have two options here:
1 if you can limit the number of rows beforehand this is the easiest to maintain approach.
SELECT
Grade,
TestID,
Permnum,
QID,
CA,
SA,
QuestionNumber,
CASE WHEN SA=CA THEN 1 ELSE 0 END as VALID,
CASE WHEN SA IS NULL THEN 'B'
WHEN SA = CA THEN 'C'
ELSE 'I' END AS RESULT
FROM
(
SELECT
"Grade"=@Grade,
"TestID"=@TestID,
Permnum,
QID,
CA,
Case When QNumber = 1 Then Q1
When QNumber = 2 Then Q2
When QNumber = 3 Then Q3
.....
WHEN QNumber = 75 THEN Q75
END AS SA,
'Q'+ str(QNumber,2)AS QuestionNumber
FROM tblRCScoreImport CROSS JOIN tblRCScoreImportQNumbers
WHERE permnum = '2000022500'
) InnerQ
1a: Another alternative is to make the calculations on the fly using userdefined functions but if the set is limited it jus add the ability to change the score procedure which I am not sure how often will happen
The Second will be to use the Case Construct for ALL columns and change those to create "valid" and "result" colums directly from the Q's it has the ability to reduce the number of subqueries but I am not sure if the optimizer is going to come up with the same execution plan
, any ways such a construct is highly ureadable and that's why I would recomment option 1HTH
* Noel -
quote:
SELECTGrade,
TestID,
Permnum,
QID,
CA,
SA,
QuestionNumber,
CASE WHEN SA=CA THEN 1 ELSE 0 END as VALID,
CASE WHEN SA IS NULL THEN 'B'
WHEN SA = CA THEN 'C'
ELSE 'I' END AS RESULT
FROM
(
SELECT
"Grade"=@Grade,
"TestID"=@TestID,
Permnum,
QID,
CA,
Case When QNumber = 1 Then Q1
When QNumber = 2 Then Q2
When QNumber = 3 Then Q3
.....
WHEN QNumber = 75 THEN Q75
END AS SA,
'Q'+ str(QNumber,2)AS QuestionNumber
FROM tblRCScoreImport CROSS JOIN tblRCScoreImportQNumbers
WHERE permnum = '2000022500'
)
This first option makes perfect sense, but I am getting an "Incorrect syntax near ')'" message that points to the paren at the bottom of the code. My code looks like this now:
*************************************
Declare @Grade as smallint
Declare @TestID as Varchar(10)
Set @Grade = 0
Set @TestID = 'HMKRL3'
SELECT
Grade,
TestID,
Permnum,
QID,
CA,
SA,
QuestionNumber,
CASE WHEN SA=CA THEN 1 ELSE 0
END as VALID,
CASE
WHEN SA IS NULL THEN 'B'
WHEN SA = CA THEN 'C'
ELSE 'I'
END AS RESULT
FROM
(
SELECT
"Grade"=@Grade,
"TestID"=@TestID,
Permnum,
QID,
CA,
Case When QNumber = 1 Then Q1
When QNumber = 2 Then Q2
When QNumber = 3 Then Q3
...
When QNumber = 75 Then Q75
End AS SA,
'Q'+ str(QNumber,2)as QuestionNumber
From tblRCScoreImport Cross Join tblRCScoreImportQNumbers
Where permnum = '2000022500'
)
********************************
I've checked this a few times, and I can't figure out where the syntax error occurs. Can you see it?
Big thanks again!
CSDunn
-
When you use "(" in the FROM clause, you HAVE to ALIAS the inline view!!
in other words Select ,,,, FROM (...) ALIASNAME
your post is missing the alias
I haven't check anything 'cause I am going home now but it looks very likely that will take care of the problem
* Noel -
quote:
When you use "(" in the FROM clause, you HAVE to ALIAS the inline view!!Thanks again for all of your help, I really appreciate it!
CSDunn
, any ways such a construct is highly ureadable and that's why I would recomment option 1Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply