SQL two or more rows into one

Imagine a student and multiple professors A professor has many students The students go to class at the same time and date.

Table1

ClassName  | ProfessorName |StudentsPresent|ClassStartTime    |ClassDate  |ClassID
Philosophy | Stewart       |10             |8:00              |01/29/2011 | 1
Philosophy | Kyle          |10             |8:00              |01/29/2011 | 1
Religion   | Myke          |11             |8:30              |01/29/2011 | 2
Religion   | Nancy         |11             |8:30              |01/29/2011 | 2

How would I go about getting the following result

ClassName  | ProfessorName |StudentsPresent|Clas开发者_高级运维sStartTime    |ClassDate  |ClassID
Philosophy | Stewart,Kyle  |10             |8:00              |01/29/2011 | 1
Religion   | Myke,Nancy    |11             |8:30              |01/29/2011 | 2

As we speak I am researching cursors.

---

Your table seems to be very denormalized. I've heard that cursors are a terrible thing within SQL Server and should be avoided if you can. With it how it is, put into a table called Schedule, this is how I might fulfill your request.

DECLARE @Schedule TABLE
(
    ClassName VARCHAR(50)
    , ProfessorName VARCHAR(50)
    , StudentsPresent INT
    , ClassStartTime TIME
    , ClassDate DATE
    , ClassID INT
);

INSERT INTO @Schedule
VALUES ('Philosophy', 'Stewart', 10, '8:00', '20110129', 1)
    , ('Philosophy', 'Kyle', 10, '8:00', '20110129', 1)
    , ('Religion', 'Myke', 11, '8:30', '20110129', 2)
    , ('Religion', 'Nancy', 11, '8:30', '20110129', 2);

WITH UniqueClasses AS
(
    SELECT DISTINCT
        ClassName
        , ClassStartTime
        , StudentsPresent
        , ClassDate
        , ClassID
    FROM @Schedule
)
SELECT
    ClassName
    ,   (
            STUFF((
                    SELECT
                        ',' + ProfessorName
                    FROM @Schedule Schedule
                    WHERE Schedule.ClassID = UniqueClasses.ClassID
                    FOR XML PATH('')
                ) , 1, 1, '')
        )AS ProfessorName
    , StudentsPresent
    , StudentsPresent
    ,  ClassStartTime
    , ClassDate
    , ClassID
FROM UniqueClasses

---

This might work for you but i think just in the case you showed.

BEGIN
        DECLARE @c CURSOR 
        DECLARE @PrevProfName VARCHAR(250) , @PrevClassDateID INT

        DECLARE @NewTable1 TABLE(
            ClassName           VARCHAR(200),
            ProfessorName       VARCHAR(200),
            StudentsPresent     INT,
            ClassStartTime      DATETIME,
            ClassDateClassID    INT)

        DECLARE 
            @ClassName          VARCHAR(200),
            @ProfessorName      VARCHAR(200),
            @StudentsPresent        INT,
            @ClassStartTime     DATETIME,
            @ClassDateClassID   INT

        OPEN @c SELECT * FROM @NewTable1 ORDER BY ClassDateClassID

        FETCH NEXT FROM @c INTO @ClassName, @ProfessorName, @StudentsPresent, @ClassStartTime, @ClassDateClassID

        WHILE @@FETCH_STATUS = 0
            BEGIN
                IF @PrevProfName <> '' AND @PrevClassDateID > 0
                    BEGIN
                        IF @PrevClassDateID = @ClassDateClassID
                            BEGIN
                                SET @PrevProfName = @PrevProfName + ',' + @ProfessorName
                            END
                        ELSE
                            BEGIN
                                INSERT INTO @NewTable1 VALUES(@ClassName, @PrevProfName, @StudentsPresent, @ClassStartTime, @PrevClassDateID)
                                SET @PrevProfName = @ProfessorName
                                SET @PrevClassDateID = @ClassDateClassID
                            END
                    END
                ELSE
                    BEGIN
                        SET @PrevClassDateID = @ClassDateClassID
                        SET @PrevProfName = @ProfessorName
                    END

                FETCH NEXT FROM @c INTO @ClassName, @ProfessorName, @StudentsPresent, @ClassStartTime, @ClassDateClassID
            END
        CLOSE @c
        DEALLOCATE @c

        SELECT * FROM @NewTable1
    END