elschott
asked on
SQL Statement for the following
I have a table that currently holds student subject enrolments with the following fields
Student_ID, Subject_ID
data could look as follows
1001, 11001
1001, 11023
1001, 11049
1002, 11023
1002, 11046
1003, 11023
etc
So a student can appear in the list a variable number of times depending on how many subects they are enrolled in some students may be in 10 subjects some maybe in as little as one. I want to be able to transpose the information stored in this table and export it in a linear format something similar to.
Student 1, subject 1, subject 2, subject 3 ... subject n
student 2, subject 1, ,,,subject n
and so on, I assume this would be pretty common in real world apps just wondering what the sql might look like to generate this output.
Thanks for any assistance
Student_ID, Subject_ID
data could look as follows
1001, 11001
1001, 11023
1001, 11049
1002, 11023
1002, 11046
1003, 11023
etc
So a student can appear in the list a variable number of times depending on how many subects they are enrolled in some students may be in 10 subjects some maybe in as little as one. I want to be able to transpose the information stored in this table and export it in a linear format something similar to.
Student 1, subject 1, subject 2, subject 3 ... subject n
student 2, subject 1, ,,,subject n
and so on, I assume this would be pretty common in real world apps just wondering what the sql might look like to generate this output.
Thanks for any assistance
with nth number of subject, you will need vba code to this. Is this an option?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Damn!! nothing is ever easy, yes vb is an option currently reviewing Sharath 123's solution to see if it would suit my needs
Hello elschott,
Here is a slightly different take. It uses VBA, as one must for this request.
(Note: I have *not* seen Sharath's suggestion, as I do not feel like registering at that site to retrieve the file. If
my suggestion is substantially the same as his, then please feel free to ignore this.)
Add this code to a regular VBA module:
Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
Optional Delimiter As String = ", ", Optional Distinct As Boolean = True, _
Optional Sort As String = "Asc", Optional Limit As Long = 0)
Dim rs As DAO.Recordset
Dim SQL As String
Dim ThisItem As String
Dim FieldCounter As Long
On Error GoTo ErrHandler
DConcat = Null
SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & IIf(Limit > 0, "TOP " & Limit & " ", "") & _
ConcatColumns & " " & _
"FROM " & Tbl & " " & _
IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
IIf(Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", "")
Set rs = CurrentDb.OpenRecordset(SQ L)
With rs
Do Until .EOF
ThisItem = ""
For FieldCounter = 0 To rs.Fields.Count - 1
ThisItem = ThisItem & Delimiter & Nz(rs.Fields(FieldCounter) .Value, "")
Next
ThisItem = Mid(ThisItem, Len(Delimiter) + 1)
DConcat = Nz(DConcat, "") & Delimiter & ThisItem
.MoveNext
Loop
.Close
End With
If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter) + 1)
GoTo Cleanup
ErrHandler:
DConcat = CVErr(Err.Number)
Cleanup:
Set rs = Nothing
End Function
You could then run a query like this:
SELECT Student_ID, DConcat("Subject_ID", "SomeTable", "[Student_ID] = " & Student_ID) AS Subjects
FROM SomeTable
GROUP BY Student_ID
If Student_ID is text:
SELECT Student_ID, DConcat("Subject_ID", "SomeTable", "[Student_ID] = '" & Student_ID & "'") AS Subjects
FROM SomeTable
GROUP BY Student_ID
Regards,
Patrick
Here is a slightly different take. It uses VBA, as one must for this request.
(Note: I have *not* seen Sharath's suggestion, as I do not feel like registering at that site to retrieve the file. If
my suggestion is substantially the same as his, then please feel free to ignore this.)
Add this code to a regular VBA module:
Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
Optional Delimiter As String = ", ", Optional Distinct As Boolean = True, _
Optional Sort As String = "Asc", Optional Limit As Long = 0)
Dim rs As DAO.Recordset
Dim SQL As String
Dim ThisItem As String
Dim FieldCounter As Long
On Error GoTo ErrHandler
DConcat = Null
SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & IIf(Limit > 0, "TOP " & Limit & " ", "") & _
ConcatColumns & " " & _
"FROM " & Tbl & " " & _
IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
IIf(Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", "")
Set rs = CurrentDb.OpenRecordset(SQ
With rs
Do Until .EOF
ThisItem = ""
For FieldCounter = 0 To rs.Fields.Count - 1
ThisItem = ThisItem & Delimiter & Nz(rs.Fields(FieldCounter)
Next
ThisItem = Mid(ThisItem, Len(Delimiter) + 1)
DConcat = Nz(DConcat, "") & Delimiter & ThisItem
.MoveNext
Loop
.Close
End With
If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter) + 1)
GoTo Cleanup
ErrHandler:
DConcat = CVErr(Err.Number)
Cleanup:
Set rs = Nothing
End Function
You could then run a query like this:
SELECT Student_ID, DConcat("Subject_ID", "SomeTable", "[Student_ID] = " & Student_ID) AS Subjects
FROM SomeTable
GROUP BY Student_ID
If Student_ID is text:
SELECT Student_ID, DConcat("Subject_ID", "SomeTable", "[Student_ID] = '" & Student_ID & "'") AS Subjects
FROM SomeTable
GROUP BY Student_ID
Regards,
Patrick
ASKER
A lot of worthy suggestions, but this worked well for me
Thanks for everyone's help
Thanks for everyone's help
SELECT Student_ID, Subject_ID FROM StudentsTable ORDER BY Student_ID
And use some language like php, asp, asp .NET, etc, to present the results the way You want it. You would loop through recordset and write Subject_ID fields associated with Student_ID until the value of Student_ID change. Then You start a new line and repeat the process for new Student_ID. Hope this helps.
Regards