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
精彩评论