October 31, 2007 at 3:08 pm
I have a query whose results are as follows:
Student Period # of Abs
Student1 2A1
Student1 3A2
Student1 4A3
Student1 8B1
Student1 5B1
Student2 7B1
Student3 1A2
Student4 1A2
Student4 2A2
Student4 5B 4
Student4 6B1
What I need is:
Student 1A 2A 3A 4A 5B 6B 7B 8B
Student1 1 2 3 1 1 1
Student2 1
Student3 2
Student4 2 2 4 1
------------------------------------------------
SELECT
RTRIM(studemo.lastname) + ', ' + RTRIM(studemo.firstname) + ' ' + RTRIM(studemo.middlename) AS Student,
trkper.periodc +
(case mstmeet.cycldayn
when '1' then 'A'
when '2' then 'B'
end) as [Period],
Count (*) as Total
FROM
studemo INNER JOIN
stusched ON studemo.suniq = stusched.suniq INNER JOIN
attend ON stusched.scduniq = attend.scduniq INNER JOIN
mstmeet ON stusched.meetuniq = mstmeet.meetuniq INNER JOIN
mstsched ON mstmeet.mstuniq = mstsched.mstuniq INNER JOIN
trkcrs ON mstsched.trkcrsuniq = trkcrs.trkcrsuniq INNER JOIN
course ON trkcrs.crsuniq = course.crsuniq INNER JOIN
facdemo ON mstsched.funiq = facdemo.funiq INNER JOIN
track ON trkcrs.trkuniq = track.trkuniq INNER JOIN
trkper ON track.trkuniq = trkper.trkuniq AND mstmeet.periodn = trkper.periodn
WHERE
attend.attendc in ('T','X')
and track.trkuniq = 77
and ddate between '10/01/2007' and '10/15/2007'
Group by
track.schoolc,
studemo.suniq,
RTRIM(studemo.lastname) + ', ' + RTRIM(studemo.firstname) + ' ' + RTRIM(studemo.middlename),
RTRIM(facdemo.lastname) + ', ' + RTRIM(facdemo.firstname),
course.descript,
course.coursec,
mstsched.sectionn,
trkper.periodc,
mstmeet.cycldayn
order by
RTRIM(studemo.lastname) + ', ' + RTRIM(studemo.firstname) + ' ' + RTRIM(studemo.middlename),
mstmeet.cycldayn,
trkper.periodc,
course.coursec,
mstsched.sectionn
--------------------------------------------------
I'm not having much luck with the pivot table example in BOL.
November 1, 2007 at 6:47 am
Opus,
You'll have to modify this to fit your needs, but this should get you there.
DECLARE @studInfo TABLE (student VARCHAR(10), period CHAR(2), absents INT)
INSERT @studInfo
SELECT 'Student1', '2A', 1 union
select 'Student1', '3A', 2 union
select 'Student1', '4A', 3 union
select 'Student1', '8B', 1 union
select 'Student1', '5B', 1 union
select 'Student2', '7B', 1 union
select 'Student3', '1A', 2 union
select 'Student4', '1A', 2 union
select 'Student4', '2A', 2 union
select 'Student4', '5B', 4 union
select 'Student4', '6B', 1
SELECT
student
,ISNULL([1A] , 0) AS [1A]
,ISNULL([2A] , 0) AS [2A]
,ISNULL([3A] , 0) AS [3A]
,ISNULL([4A] , 0) AS [4A]
,ISNULL([5B] , 0) AS [5B]
,ISNULL([6B] , 0) AS [6B]
,ISNULL([7B] , 0) AS [7B]
,ISNULL([8B] , 0) AS [8B]
FROM
(SELECT
student
,period
,absents
FROM
@studInfo) AS dataToPivotTable
PIVOT
(SUM(absents) -- value to aggregate
FOR period -- what are your horizontal columns (fields)
IN ([1A]
,[2A]
,[3A]
,[4A]
,[5B]
,[6B]
,[7B]
,[8B])
) AS whatToPivotOnTable
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 1, 2007 at 7:07 am
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply