February 22, 2010 at 9:14 am
I have two tables
TABLE 1:STUDENTS
STUDIDSTUDNAME
UA001Roberto GILL
UA002Jack De Nero
UA003Steven Langa
UA003Moses
TABLE 2:MARKS
MARKSIDSTUDID SUBJECTS MARKS
1 UA001 ENGLISH 60
2 UA002 BIOLOGY 80
3 UA003 MATH 56
4 UA003 ENGLISH 34
5 UA002 MATH 89
6 UA004 BIO 23
7 UA005 MATH 56
8 UA001 PHYSICS 55
9 UA001 BIOLOGY 87
10 UA003 PHYSICS 56
I want to a dynamic crosstab query to return for me the following output
STUDID STUDENTS ENGLISH MATH BIO PHYSICS AVERAGE
UA001 Roberto GILL 60 0 87 55 (Cal the average)
UA002 Jack De Nero 0 89 80 0 ""
UA003 Steven Langa 34 56 0 56 ""
UA004 Moses
I tried the code below but can't get anything like what i want.
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.Subjects
FROM Marks AS t2
ORDER BY '],[' + t2.Subjects
FOR XML PATH('')
), 1, 2, '') + ']'
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT t2.StudID, '+
@cols +'
FROM
(SELECT MarksId
, t1.Subjects
, t1.Marks
FROM Students AS t2
JOIN Marks AS t1 ON t1.StudId = t2.StudId) p
PIVOT
(
SUM([Marks])
FOR Subjects IN
( '+
@cols +' )
) AS pvt
ORDER BY MarksId;'
exec(@query)
I get error message below
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "t2.StudID" could not be bound.
Can some1 please help me to point where the error is or paste me a dynamic crosstab that can do the work?
Thanks.
Robert
February 22, 2010 at 10:37 am
StudID isn't part of your subquery and you cannot reference t.2 in your outer query.
Try the following:
SET @query = N'SELECT StudID, '+
@cols +'
FROM
(SELECT t2.StudID
, MarksId
, t1.Subjects
, t1.Marks
FROM Students AS t2
JOIN Marks AS t1 ON t1.StudId = t2.StudId) p
PIVOT
(
SUM([Marks])
FOR Subjects IN
( '+
@cols +' )
) AS pvt
ORDER BY MarksId;'
February 22, 2010 at 10:14 pm
thanks alot.
I figured it out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply