1--Create a test table2 CREATE TABLE [dbo]. [Testrows2columns] (3[Id] [int] IDENTITY (1,1) not NULL,4[UserName] [nvarchar] ( -) NULL,5[Subject] [nvarchar] ( -) NULL,6[Source] [Numeric] ( -,0) NULL7 )8 GO9 Ten--inserting test Data One INSERT into [Testrows2columns] ([Username],[subject],[source]) ASELECT N'Zhang San'N'language', -UNION All -SELECT N'John Doe'N'Mathematics', -UNION All -SELECT N'Harry'N'English', theUNION All theSELECT N'Harry'N'Mathematics', theUNION All -SELECT N'Harry'N'language', $UNION All -SELECT N'John Doe'N'language', theUNION All -SELECT N'Zhang San'N'English', - + GO - +SELECT *From [Testrows2columns] A at - - ---1PassSelectAccumulate -DECLARE @sql_col VARCHAR (8000) -SELECT @sql_col = ISNULL (@sql_col +',',"') +QUOTENAME ([Subject]) from Testrows2columns in GROUP by [Subject] - to SELECT @sql_col + - the * $--2Through the FOR XML path ("') Merge string RecordsPanax Notoginseng SELECT - STUFF ( the(SELECT'#'+Subject + From testrows2columns AWHERE UserName ='Harry' theFOR XML Path ("') +),1,1,"' - ) $ $ ---3Grouping merged string Records - SELECT the UserName, -Subject = (Wuyi STUFF ( the(SELECT'#'+Subject - From testrows2columns WuWHERE UserName =A.username -FOR XML Path ("') About),1,1,"' $ ) - ) - From testrows2columns A -GROUP by UserName
SQL Server string multiple rows merged into one line