Combining data in one row from rows with same unique ID

[SophyC]

Hello



I'm hoping anybody can help me with this.

I have two tables, School and Class. A school can have multiple classes (one to many relationship). They are connected through the unique id School_ID.

At the moment, I have a simple query where the output looks like this:

School_ID	School_name	Class_name	Class_nrOfStudent
123	NameOfSchool1	6A	15
123	NameOfSchool1	6B	12
456	NameOfSchool2	5A	5
456	NameOfSchool2	5B	10

But what I actually need is this:

School_ID	School-name	Class_name_1	Class_nrOfStudent_1	Class_name_2	Class_nrOfStudent_2
123	NameOfSchool1	6A	15	6B	12
456	NameOfSchool2	5A	5	5B	10

Can anybody help me with how I can achieve this?
Thank you very much!

[Gicu]

Cross-tab query? Or concatenate (http://allenbrowne.com/func-concat.html)

Cheers,

[CJ_London]

only way would be using 4 queries - 1 to determine a number for the 'class_name_X' column header, 2 crosstabs (one each for name and number) and a final one to join on schoolid

Qry1 would be something like

SELECT A.School_ID, A.Class_Name, count(B.Class_Name) as ClassNo
FROM tblSchools A INNER JOIN tblSchools B ON A.school_ID=B.school_ID
WHERE B.Class_Name<=A.Class_Name

Qry2 would be something like

TRANSFORM First(A.Class_Name) AS [FirstOfClass_Name]
SELECT SchoolID
FROM tblSchools A INNER JOIN Qry1 B ON A.School_ID=B.School_ID AND A.Class_Name=B.Class_Name
GROUP BY A.School_ID
PIVOT B.ClassNo;

Qry3 would be similar, just substitute Class_nrOfStudent for Class_Name

Qry4

I'll leave to you but join qry2 to queries 3 and 4 on school_id. The reason I'm leaving it is because you'll need to cater for things like different schools having different numbers of classes