Subquery results in comma separated format

You can use FOR XML along with a few REPLACEs as shown here:

SELECT column1, 
       column2, 
       CourseRequests=COALESCE(
                        REPLACE(REPLACE(REPLACE((
                       SELECT crse_name 
                       FROM   (
                                         SELECT 1, 22,  'first',  0
                               UNION ALL SELECT 2, 22,  'second', 1
                               UNION ALL SELECT 3, 22,  'third',  0
                               UNION ALL SELECT 4, 555, 'first',  1
                       ) SCR (id, stud_pk, crse_name, delt_flag)
                       WHERE  SCR.stud_pk = MS.tt_stud_pk 
                       AND SCR.delt_flag = 0
                       FOR XML PATH('')
                       ),'</crse_name><crse_name>', ','),
                     '</crse_name>', ''), -- remove end tag
                     '<crse_name>', ''), -- remove beginning tag
                      ''), -- optional COALESCE to ensure no NULLs
       column4 
FROM   (
                   SELECT  1, 'a', 'b', '2014-01-01'
         UNION ALL SELECT 22, 'd', 'e', '2014-02-02'
       ) MS (tt_stud_pk, column1, column2, column4)

Output:

column1 column2 CourseRequests  column4  
a       b                       2014-01-01  
d       e       first,third     2014-02-02  

Explanation:
The FOR XML PATH('') flattens the result of the sub-query to be:
<crse_name>first</crse_name><crse_name>third</crse_name>

1. The first REPLACE converts just the end-tag/beginning-tag combinations that are only found between values (i.e. where the commas go)
2. The second REPLACE removes the ending tag (can't be done before the first REPLACE)
3. The third REPLACE removes the beginning tag (can't be done before the first REPLACE)

Note:
There might be a slightly more elegant way to do the XML stuff so you don't need all of the REPLACEs, but not sure and this does work.

I'm pretty sure you can't do this with a single query, and I'm not entirely certain the tactic I've come up with is a legitimate tactic--meaning, if it is undocumented, a future version of SQL might not support this. With that said:

Start with the following:

DECLARE @List varchar(max)

SELECT @List = isnull(@List + ', ', '') + InnerCourseRequests
 from tor_studentcrserequest
 where stud_pk = <TestValue>
  and delt_flag = 0

PRINT @List

This will generate a comma-delimited list of all InnerCourseRequests from the tor_studentcrserequest table for a single stud_pk.

Next, turn it into a function:

DROP FUNCTION phkTest
GO
CREATE FUNCTION phkTest (@stud_pk  int)  --  Change datatype, if not int
RETURNS varchar(max)
AS
 BEGIN
    DECLARE @List varchar(max)

    SELECT @List = isnull(@List + ', ', '') + InnerCourseRequests
     from tor_studentcrserequest
     where stud_pk = @stud_pk
      and delt_flag = 0

    RETURN @List
 END
GO

(Add a second parameter for delt_flag, if that might vary somehow)

And add that to a query:

SELECT distinct tt_stud_pk, dbo.phkTest(stud_pk)
 from tbl_mainstudent

(I wrote all this using one of my tables, then cut-and-paste your table/columns in, so there may be some syntax issues to deal with.)

There may be ways to improve performance for big tables (OUTER APPLY, select distinct before calling the function, and so forth), and it's entirly likely that this might best be done via procedural code by whatever's querying the data in the first place.