SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to format data for a grid
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rdprecure
Starting Member

7 Posts

Posted - 07/02/2013 :  03:06:30  Show Profile  Reply with Quote
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:


Edited by - rdprecure on 07/02/2013 03:07:46

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/02/2013 :  03:18:59  Show Profile  Reply with Quote


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)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rdprecure
Starting Member

7 Posts

Posted - 07/02/2013 :  09:12:16  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/02/2013 :  09:46:14  Show Profile  Reply with Quote
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000