sohairzaki2005
asked on
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
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
Hi,
This shows an example. Modify to suit your table names
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)
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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#<
<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.