How to format data for a grid - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to format data for a grid

Author

Topic

rdprecure
Starting Member

7 Posts

Posted - 2013-07-02 : 03:06:30

Hello,

I need to pull data from a couple of tables to populate a grid in VB.Net. I know how to select the data I need, just not how best to format it for populating the grid (pivot, etc). Any help would be very much appreciated.

Here are the two tables:

CREATE TABLE Students(
	ID int NOT NULL,
	FirstName varchar(25) NOT NULL,
	LastName varchar(25) NOT NULL,
	ProgramID int NOT NULL)

CREATE TABLE TestHistory(
	ID int NOT NULL,
	TestID bigint NOT NULL,
	StudentID bigint NOT NULL ,
	TestDate datetime NOT NULL,
	TestScore real NOT NULL)

insert into Students (ID,FirstName,LastName,ProgramID)
values (1,'Zach','Grant',23)
insert into Students (FirstName,LastName,ProgramID)
values (2,'Nick','Riley',23)
insert into Students (FirstName,LastName,ProgramID)
values (3,'Veronica','Rosalez',23)
insert into Students (FirstName,LastName,ProgramID)
values (4,'Debra','Smithers',23)
insert into Students (FirstName,LastName,ProgramID)
values (5,'Robert','Sutton',23)
insert into Students (FirstName,LastName,ProgramID)
values (6,'Cody','Swain',23)
insert into Students (FirstName,LastName,ProgramID)
values (7,'Crystal','Rivers',23)
insert into Students (FirstName,LastName,ProgramID)
values (8,'Gabe','Martinez',23)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (1,5,1,'1/15/2013',95)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (2,10,1,'2/15/2013',86)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (3,15,1,'3/15/2013',90)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (4,20,1,'4/15/2013',80)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (5,5,2,'1/15/2013',76)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (6,10,2,'2/15/2013',78)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (7,15,2,'3/15/2013',77)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (8,20,2,'4/15/2013',71)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (9,5,3,'1/15/2013',85)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (10,10,3,'2/15/2013',75)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (11,15,3,'3/15/2013',95)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (12,20,3,'4/15/2013',65)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (13,5,4,'1/15/2013',54)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (14,10,4,'2/15/2013',67)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (15,15,4,'3/15/2013',68)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (16,20,4,'4/15/2013',60)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (17,10,5,'2/15/2013',90)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (18,15,5,'3/15/2013',96)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (19,20,5,'4/15/2013',91)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (20,5,6,'1/15/2013',88)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (21,15,6,'3/15/2013',86)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (22,20,6,'4/15/2013',80)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (23,5,7,'1/15/2013',95)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (24,10,7,'2/15/2013',98)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (25,20,7,'4/15/2013',93)

insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (26,5,8,'1/15/2013',71)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (27,10,8,'2/15/2013',77)
insert into TestHistory (ID,TestID,StudentID,TestDate,TestScore)
values (28,15,8,'3/15/2013',85)

To summarize the data in the tables:
- The Students table contains 8 students
- The TestResults table contains the test results of 4 tests, with students 1-4 taking all 4 tests and students 5-8 taking only 3 tests each.

Once populated, the grid should look something like:

[/img]

Any suggestions would be very much appreciated. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 03:18:59

[code]

DECLARE @Datelist varchar(2000),@SQL varchar(8000)
SET @DateList= STUFF((SELECT DISTINCT ',[' + CONVERT(varchar(11),TestDate,101) + ']' FROM TestHistory ORDER BY
',[' + CONVERT(varchar(11),TestDate,101) + ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT *
FROM
(
SELECT *
FROM Students s
INNER JOIN TestHistory th
ON th.StudentID = s.StudentID
)t
PIVOT(MAX(TestScore) FOR TestDate IN (' + @Datelist + '))p'

EXEC (@SQL)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rdprecure
Starting Member

7 Posts

Posted - 2013-07-02 : 09:12:16

Wow! Didn't expect actual code as a response. With a minor modification it worked perfectly.

Changed "select *" to "select lastname,testdate,testscore" to address a "column specified multiple times" error.

Many thanks Visakh for your timely and helpful response.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 09:46:14

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources