Link to home
Start Free TrialLog in
Avatar of sohairzaki2005
sohairzaki2005Flag for United States of America

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
Avatar of silvera21
silvera21
Flag of United States of America image

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.
Avatar of cyberkiwi
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

Avatar of sohairzaki2005

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
ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial