stored procedure to produce pivot table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure to produce pivot table

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-21 : 13:27:51

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
I want to create a stored procedure that will produce the following result set in the format mentioned before

sarah

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-21 : 13:51:30

But why you don't want to use pivot ?
I know there must be reason but if you tell us the reason we may give better suggestions.

Example using pivot:

Create table #Assign
(assignID Int,
assignName Varchar(255),
points int )

Create table #assignpoint
(assignID Int,
studentid int,
score int )

Insert into #Assign
Select 1,'assginname1',10 union
Select 2,'assginname2',10 union
Select 3,'assginname3',10

Insert into #assignpoint
Select 1,1, 5 union
Select 2,1, 8 union
Select 3,1, 3 union
Select 1,2, 8 union
Select 2,2, 9 union
Select 2,3, 6

Select * from
(
Select Ap.StudentId, A.AssignId, Ap.Score,Ap.StudentId as Stud1
from #assignpoint ap inner join #Assign A
on Ap.assignID = a.assignID) p
Pivot(
Sum(Score)
FOR AssignId IN (
[1],[2],[3])
) AS PVT
ORDER BY 1

You can also refer the below link on using dynamic pivot:
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-21 : 14:37:09

Thanks
But I do not know the number of assignments before hand so I can not say [1], [2],[3]
Can you help me with that

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-21 : 14:42:04

Thanks
I am reading about dynamic pivot now

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-21 : 14:45:04

Is it possible to edit the tables underneath the pivoting process using stored procedure

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 23:35:44

something like:-


DECLARE @AssignmentList varchar(max),@SQL varchar(8000)
SELECT @AssignmentList='[' + STUFF((SELECT '],['+ assignName
                              FROM assign
                              FOR XML PATH('')),1,3,'') + ']'

SET @SQL='SELECT studentid,' + @AssignmentList + '
          FROM
          (
          SELECT ap.studentid,ap.score,a.assignName
           FROM assignpoint ap
           JOIN assign a
           ON a.assignID=ap.assignID
          )m
          PIVOT (SUM(Score) FOR assignName IN (' + @AssignmentList + ')p'

EXEC (@SQL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources