T_sql to merge a row of rows of data into one row

T_sql to merge a row of rows of data into one row

SQL Server in the Data migration and report processing will encounter a column of multiple rows of data stitching into a string of cases, in order to deal with this problem, on the Internet to find some relevant information, provide two ways for friends who encounter similar problems to reference, but also to deepen their impressions.

Table:sc

student	course
Zhang San	University Chinese
John Doe	University Chinese
Zhang San	Calligraphy appreciation
Zhang San	Music appreciation
John doe	Film Appreciation

The desired results are:

student	course
Zhang San	University Chinese, calligraphy appreciation, music appreciation
John doe	College Chinese, film appreciation

IF object_id (N ' SC ') is not NULL

BEGIN

DROP TABLE SC

END

ELSE

BEGIN

CREATE TABLE SC

(

Student NVARCHAR (50),

Course NVARCHAR (50)

)

INSERT into SC

SELECT N ' Zhang San ', N ' University language ' UNION all

SELECT n ' John Doe ', n ' University language ' UNION all

SELECT N ' Zhang San ', N ' calligraphy appreciation ' UNION all '

SELECT N ' Zhang San ', N ' Music appreciation ' UNION all

SELECT n ' John Doe ', n ' movie appreciation '

END

GO

(5 row (s) affected)

S

Method One: User-defined functions

CREATE FUNCTION Fn_merge (@Student NVARCHAR (50))

RETURNS NVARCHAR (50)

As

BEGIN

DECLARE @Course NVARCHAR (50)

SELECT @Course = ISNULL (@Course + ', ', ") + @Course

From SC

WHERE Student = @Student

RETURN @COURSE

END

SELECT DISTINCT [Student]

Dbo. Fn_merge ([Student]) as Course

from [dbo]. [SC]

Results:

(2 row (s) affected)

Method Two: For XML PATH

SELECT DISTINCT [Student]

, STUFF (

(

SELECT ', ' +[course]

from [dbo]. [SC]

WHERE Student = a.student

For XML PATH (")

)

, 1, 1, "

) as Course

from [dbo]. [SC] As A

Results:

(2 row (s) affected)

Link:

1. Stuff () function http://msdn.microsoft.com/zh-cn/library/ms188043.asp

Reprint: http://www.cnblogs.com/smalleyes/archive/2012/03/08/2385658.html

The above is t_sql to merge a row of multiple rows of data into a single line of all content, more attention: CPP Learning Network _cpp University
This article fixed link: CPP Learning Network _cpp University-t_sql merge a row of multiple rows of data into one row

T_sql to merge a row of rows of data into one row