SQL query inline function

Question

Post reply

SQL query inline function

stratergib_77

SSC Enthusiast

Points: 167
More actions
December 23, 2008 at 11:54 pm

#197500

I have created tableAdapters to query information from my sql server database and then later bind to a gridview on a page(my front end is ASP.NET). One table in my database (quiz table) has four columns, studentID, courseID, quizNumber, and quizGrade. So this means that a student in a given course will appear in this table n number of times depending on how many quizzes there are(quizNumber keeps track of each quiz that has been issued out). My question is how to query this data so it can be in the following format in my gridview on a asp.net page:
| Student ID | Course ID | Quiz Number 1 Grade | Quiz Number 2 Grade | Quiz Number 3 Grade |
1014566 1300Fall 98.5 100 77
1025415 1300Fall 88 98 85
instead of how I currently have them, where I get the columns in my gridview to be just like in my database, which means that each student comes out in many rows depending on the number of quizzes like this:
| Student ID | Course ID | quizNumber | quizGrade |
1014566 1300Fall 1 98.5
1014566 1300Fall 2 100
1014566 1300Fall 3 77
1025415 1300Fall 1 88
1025415 1300Fall 2 98
1025415 1300Fall 3 85
I've been reading up on inline valued functions that return tables in order to accomplish this, but no luck yet.
Any help would be greatly appreciated.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Here are some tips for posting in a way that helps people help you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D

Jeff was also nice enough to do two articles about Pivots and Crosstabs:

http://www.sqlservercentral.com/articles/T-SQL/63681/%5B/url%5D

http://www.sqlservercentral.com/articles/cross+tab/65048/%5B/url%5D

What you are looking for is probably a dynamic crosstab. I think you want the number of columns returned to change based on the number of quizzes taken. That would be covered in the second link.

You really should do this type of pivot in the presentation application rather than in T-SQL. The Dynamic crosstab will work, but T-SQL is not really well suited for this. Pivoting the result set in your .Net code will be easier and probably perform better.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Here is a sample for pivoting in C#

http://aspalliance.com/538_CodeSnip_Pivot_Tables_with_ADONET_and_Display_in_a_DataGrid_Paged_Horizontally.1

stratergib_77 SSC Enthusiast Points: 167 More actions · Answer 3

Thanks a lot Michael, that was so helpful you got me out of a jam!