Link to home
Start Free TrialLog in
Avatar of elschott
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
Avatar of R-Byter
R-Byter
Flag of Serbia image

You cant get results in that way but You can for example execute simple query like this one

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
Avatar of Rey Obrero (Capricorn1)
with nth number of subject, you will need vba code to this. Is this an option?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of elschott
elschott

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(SQL)
    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
A lot of worthy suggestions, but this worked well for me

Thanks for everyone's help