Concatenate, how to get different values from a column into one row?

I am trying to list a course number and then in one row, show the grades that course covers. I tried a concatenate, but it puts it on different rows. I want to see:

6740    , , , 10, 11, 12.

But I’m getting this:

  • JTDS error: Native SSPI library not loaded
  • Drop all extended properties on SQL Server
  • How to get first character of a string in SQL?
  • Why setting current identity value is not working for me in SQL Server 2008 R2?
  • SQL Server In Memory Tables performance for autocomplete search performance
  • Where can I find the Invoice lines/details in DynamicsAX database?
  • courseID    Grades
    6740    , , , , , 12
    6740    , , , , 11, 
    6740    , , , 10, , 
    

    This is the code I’m using (the grades are strings, not numbers):

    select distinct c.courseID
    ,case when st.grade = '7' then '7' else '' end 
    + ', '
    + case when st.grade = '8' then '8' else ''end 
    + ', '
    + case when st.grade = '9' then '9' else ''end
    + ', '
    + case when st.grade = '10' then '10' else ''end
    + ', '
    + case when st.grade = '11' then '11' else ''end
    + ', '
    + case when st.grade = '12' then '12' else '' end
    as Grades
    from course c
    inner join calendar cal on cal.calendarID = c.calendarID
    
    inner join Section s on s.courseID = c.courseID
    inner join Trial tr on tr.trialID = s.trialID and tr.calendarID =        cal.calendarID
        and tr.active = 1
    inner join Roster r on r.sectionID = s.sectionID
    inner join student st on st.personID = r.personID and st.calendarID = cal.calendarID
    inner join sectionPlacement sp on sp.sectionID = s.sectionID
    where c.calendarID in (1058, 1054)
        and c.active = 1
    

    Can someone help steer me in the right direction to get one row per course showing all the grades? I’m using Microsoft SQL 2012

    One Solution collect form web for “Concatenate, how to get different values from a column into one row?”

    Maybe something like this:

    Short explanation: This approach simulates the MySQL-Group-Concat. If you need your grades in dedicated columns you need PIVOT or GROUP BY with MAX(CASE...), or dynamic SQL.

    DECLARE @course TABLE(ID INT,CoursName VARCHAR(100));
    INSERT INTO @course VALUES(6740,'Course 6740'),(9999,'Course 9999');
    
    DECLARE @grade TABLE(ID INT,GradeName VARCHAR(100));
    INSERT INTO @grade VALUES(7,'Grade 7'),(8,'Grade 8'),(9,'Grade 9');
    
    
    DECLARE @data TABLE(CourseID INT, GradeID INT);
    INSERT INTO @data 
    VALUES
     (6740,7),(6740,8),(6740,9)
    ,(9999,7),(9999,9);
    
    SELECT d.CourseID
          ,c.CoursName
          ,STUFF(
              (
                SELECT ', ' + g.GradeName
                FROM @data AS d1
                INNER JOIN @grade AS g ON d1.GradeID=g.ID
                WHERE d1.CourseID=d.CourseID
                FOR XML PATH('')
              ),1,2,'') AS Grades
    
    FROM @data AS d
    INNER JOIN @course AS c ON d.CourseID=c.ID
    GROUP BY  d.CourseID,c.CoursName
    

    This is the result

    CourseID    CoursName     Grades
    6740        Course 6740   Grade 7, Grade 8, Grade 9
    9999        Course 9999   Grade 7, Grade 9
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.