cross table qwiz, for student administration

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

cross table qwiz, for student administration

Author

Topic

dirkjan75
Starting Member

2 Posts

Posted - 2012-06-04 : 08:45:12

Hi

we have 1 table with student details
one table with recorded conversations with these students and there mentors, linked with student_id. Students have no more than 10 conversations with there mentor

I want to create a query that shows

student_id (and name etc), conversation date 1, conversation date 2, conversation date 3 etc. When I try to create a cross table SQL reminds me that the date columns exceed the maximum of 256. How can I create columnhead that say 1 (for conversation 1) , 2 , 3 etc. and having the value in this field be the date of the conversation.

So it will look something like this

student_id conv1 conv2 conv3 conv4
1 1/1/2011 12/3/2011 15/3/2011
2 10/2/2011 1/4/2012
3 1/6/2011 1/8/2011 11/1/2012 11/11/2012
etc

is this even possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 10:04:00

so you want only four date values? if yes use


SELECT student_id,name,[1] AS Cnv1,[2] AS Conv2,[3] AS Conv3,[4] AS Conv4
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY datefield) AS Rn,*
FROM Students s
JOIN Mentors m
ON m.student_id = s.student_id
)t
PIVOT (MAX(datefield) FOR Rn IN ([1],[2],[3],[4]))p

I've assumed table and column names as i dont know the actual names. make sure you replace them with correct names in your actual query

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

dirkjan75
Starting Member

2 Posts

Posted - 2012-06-04 : 10:45:39

hI Visakh16

Well, I know that the maximum number of conversations in the mentors table is 10 (which means that a student will not have more than about 10 conversations with the mentor. But this varies per person.

the student table looks like this:
student_id achternaam
1 Aa
2 Aalders
3 Aarnink
4 Aarnoutse
5 Abdelrahman
6 Achtereekte
7 Adriaense
8 Aken

the mentor like this (field emitted)
Id student_id datum
48 1 9-10-2008
49 2 11-1-2007
50 4 27-9-2011
51 4 7-1-2010
52 4 26-10-2010
53 5 18-6-2009
54 5 1-9-2010

I work in msaccess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-04 : 12:43:15

so are you looking for msaccess of t-sql query? if former please post in access forum.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources