sql stored procedure to display result set in horizontal format

I have the following tables:
assign table (assignID, assignName, points)
table assignpoint table (assignid, studentid, score)

I want to display them in table format as follows
studentid ,assginname1, assignname2, assigname3, etc. total
1        , 90,            80,         90, etc.

How can I do that I know about pivot tables but i can not implement it in my senario mentioned above.

I am using SQL server 2008 and coldfusion
no reporting services
The display will be used also to edit the information in the tables
I want to create a stored procedure that will produce the following result set in the format mentioned before
sohairzaki2005Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

silvera21Commented:
It would be easiest to join this in a normal manner, then use coldfusion to get the format you want.
SELECT * FROM assign
INNER JOIN assignpoint
on assign.assignid = assignpoint.assignid
ORDER BY studentid ASC

order the results by student id, then use an output loop with student id as the group item.

<table>
<cfoutput query="queryname" group="studentid">
  <tr>
  <td>#queryname.studentid#</td>
  <cfoutput>
     <td>#queryname.score#</td>
  </cfoutput>
  </tr>
  </cfoutput>
</table>

That is the general idea anyways. You can make an aggregate query in a particular format, but this is much more flexible because you are separating the display from the content.
0
cyberkiwiCommented:
Hi,

This shows an example.  Modify to suit your table names
create table #assign (assignID int, assignName int, points int)
create table #assignpoint (assignid int, studentid int, score int)
insert #assign values (1,1,3)
insert #assign values (2,2,4)
insert #assign values (3,3,3)
insert #assign values (4,4,3)
insert #assignpoint select 1, 1, 1
insert #assignpoint select 1, 2, 2
insert #assignpoint select 1, 3, 4
insert #assignpoint select 2, 1, 10
insert #assignpoint select 3, 2, 20
insert #assignpoint select 3, 4, 40
;
declare @sql nvarchar(max)
select @sql = coalesce(@sql + ',', '') + '[' + convert(varchar,assignid) + ']'
from #assign
select @sql = '
select studentid, ' + @sql + '
from (
	select p.studentid, a.assignid, score
	from #assign a inner join #assignpoint p on a.assignID = p.assignID
) S
pivot
(
sum(score)
for assignid in (' + @sql + ')
) as PVT'
exec (@sql)

Open in new window

0
sohairzaki2005Author Commented:
Thanks That was really helpful.
But if I want to display the score and under neath it the letter grade whic compares the assign point and the student score
Is it possible to use this grid to edit the underlying tables
Thanks again
0
cyberkiwiCommented:
>> Is it possible to use this grid to edit the underlying tables
Nope.  You need to create insert/update statements to do that in your programming environment

Give the attached a go
--drop table #assign
--drop table #assignpoint
create table #assign (assignID int, assignName varchar(10), points int)
create table #assignpoint (assignid int, studentid int, score int)
insert #assign values (1,'asgn1',3)
insert #assign values (2,2,4)
insert #assign values (3,'test 3',3)
insert #assign values (4,4,3)
insert #assignpoint select 1, 1, 1
insert #assignpoint select 1, 2, 2
insert #assignpoint select 1, 3, 4
insert #assignpoint select 2, 1, 10
insert #assignpoint select 3, 2, 20
insert #assignpoint select 3, 4, 40
;
declare @sql nvarchar(max)
select @sql = coalesce(@sql + ',', '') + '[' + convert(varchar,assignName) + ']'
from #assign
select @sql = '
select studentid, ' + @sql + '
from (
	select p.studentid, a.assignName, score
	from #assign a inner join #assignpoint p on a.assignID = p.assignID
) S
pivot
(
sum(score)
for assignName in (' + @sql + ')
) as PVT'
exec (@sql)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.