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?
 
cyberkiwiConnect With a Mentor Commented:
>> 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.