SQL two or more rows into one

Question

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|ClassStartTime    |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.

Related: http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column — OMG Ponies, Jan 28 '11 at 23:06
@Abe: I doubt it. This is a really hard problem to solve in T-SQL (of course I guess I AM assuming this is for SQL Server). OMG Ponies reference to the stackoverflow question that this is a duplicate of has a nice demonstration of the way to get this type of result. — richard, Jan 28 '11 at 23:19
This is a work assignment. Right now I am stuck on this. Also OMG ponies thanks for the link but that does not solve my problem or gives me any idea how to tackle this. — hidden, Jan 28 '11 at 23:22
Here is the logic: IF ClassName = ClassNameNextRow and ProfessorName = ProfessorNameNextRow and StudentsPresent=StudentsPresentNextRow and ClassStartTime=ClassStartTimeNextRow and ClassDate=ClassDateNextRow and ClassID = ClassIDNextRow then select ProfessorName update current row with next row professors name — hidden, Jan 28 '11 at 23:28

score 2 · Accepted Answer · answered Jan 29 '11 at 03:54

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

The table comes from a highly denormalized database[s]. This is the finished product of a stored procedure before it can be executed into a report(rdl). Your answer limits is not generic enough but thank you for the effort! — hidden, Jan 30 '11 at 06:30
@user47533: I can make it more generic, but I would need more information. I had only a starting and ending table, and this converts the two. What cases does this not handle that you need to account for? — Jeremy Pridemore, Jan 31 '11 at 18:56

score 1 · Answer 2 · answered Jan 29 '11 at 00:08

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

A cursor is not strictly necessary. See my answer for one way to do it without. Other ways exist as well. ^.^ — Jeremy Pridemore, Jan 29 '11 at 03:56
if i cant vote i will sure vote for you, but i think this will work only with sql server 2008. — AlaaL, Jan 29 '11 at 11:43
@TheDarkLord: Thanks. I'm not sure about the server version it works on. Whenever FOR XML PATH is valid. Since he did not specify I tried to implement it in the coolest way I knew. :) — Jeremy Pridemore, Jan 31 '11 at 18:59

SQL two or more rows into one

2 Answers2