Pivot Query Example - does one exist with Datasource available?

Hi All,
I posted an earlier question here a w ago, it was never answerd.. I'll
try to simplify it a bit.
I need to get an example of a Pivot Query, with a datasource. i've looked
at alot of them online, and They don't really make sense...either they're to
o
general, or they're without datasource. I need to see one in a real world
environment..
Can someone point me to an example? I've been fighting with the one in my
previous post, and i think i'm just digging myself deeper into a hole not
knowing what i'm adjusting and tweaking. Im using stored procedures to do
this...if that makes a differnce. It's not that I'm a unskilled in SQL
Server, I'm just totally lost with the Pivot Querys.
Thanks
Daniel Regalia
www.krushradio.com - Internet Radio for the rest of usHello Daniel,

> I posted an earlier question here a w ago, it was never answerd..
> I'll try to simplify it a bit.
> I need to get an example of a Pivot Query, with a datasource. i've
> looked at alot of them online, and They don't really make
> sense...either they're too general, or they're without datasource. I
> need to see one in a real world environment..
> Can someone point me to an example? I've been fighting with the one
> in my previous post, and i think i'm just digging myself deeper into a
> hole not knowing what i'm adjusting and tweaking. Im using stored
> procedures to do this...if that makes a differnce. It's not that I'm
> a unskilled in SQL Server, I'm just totally lost with the Pivot
> Querys.
Are you working with Reporting Services, is that what you mean by data sourc
e?
Otherwise, does this what you're looking for?
-- Prep tables
create table dbo.marks
(
studentID char(2),
subjectID tinyint,
score tinyint
)
go
create table dbo.subjects
(
subjectID tinyint,
subjectName varchar(15)
)
go
create table dbo.students
(
studentID char(2),
studentName varchar(15)
)
go
-- Spin up data
insert into dbo.marks values('A1','1','75')
insert into dbo.marks values('A1','2','70')
insert into dbo.marks values('A1','3','80')
insert into dbo.marks values('A1','4','85')
insert into dbo.marks values('A1','5','90')
insert into dbo.marks values('A2','1','70')
insert into dbo.marks values('A2','2','75')
insert into dbo.marks values('A2','3','80')
insert into dbo.marks values('A2','4','90')
insert into dbo.marks values('A2','5','80')
insert into dbo.subjects values(1,'Maths')
insert into dbo.subjects values(2,'Science')
insert into dbo.subjects values(3,'Social')
insert into dbo.subjects values(4,'English')
insert into dbo.subjects values(5,'Tamil')
insert into dbo.students values('A1','Selva')
insert into dbo.students values('A2','Akbar')
go
-- solution
with m(studID,studName,subject,score) as
(
select s.studentID,s.StudentName,b.subjectName,m.score
from dbo.students s
join dbo.marks m on s.studentID = m.studentID
join dbo.subjects b on m.subjectID = b.subjectID
)
select StudID,StudName,Maths,Science,Social,Eng
lish,Tamil
from m
pivot
(
max(score)
for subject in ([Maths],[Science],[Social],[English],[T
amil])
) p
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/