S? Q? L? S? E? R? V? E? R? End? Result? Set? Transfer? Is it? Word? ? String

for example, the returned result of a select username from employee is username, Zhang San, Li Si, and Wang Wu.
How to output a string similar to: Zhang San, Li Si, Wang wua:
declare @ s varchar (1000)
select @ s = isnull (@ s + ',','') + username from employee select @ s as username
--> Generate a test data table: employee
if not object_id ('[employee]') is null drop table [employee] Go
Create Table [employee] ([username] nvarchar (10 )) insert [employee]
select n 'zhang san' Union all select n 'Li si' Union all select n' Wang wu' go
-- select * from [employee]
--> the SQL query is as follows:
declare @ s varchar (100)
select @ s = isnull (@ s + ',','') + [username] from [employee]

Zuolo: The preceding query statement cannot add order by for sorting. Otherwise, only the last record can be obtained.
Print @ s -- result:
-- Zhang San, Li Si, and Wang Wu
How can I help you? How to Set
'01, 04 'is converted to the result set of the 1 column 01 02 03 04

/* Split the string **/
-- Split a single column + sequence number
-- Method 1: Use a digital auxiliary table
If object_id ('fn _ splittsql ') is not null drop function fn_splittsql go
Create Function DBO. fn_splittsql
(@ S nvarchar (max), @ split nchar (1) returns table as return select
N-len (replace (left (array, n), @ split, '') + 1 as RN, substring (array, n,
Charindex (@ split, array + @ split, n)-N) as col from (select @ s as array) as d join DBO. Nums
On n <= Len (array)
And substring (@ split + array, N, 1) = @ split; go
-- Method 2: directly split
If object_id ('f _ Split ') is not null drop function f_split go
Create Function f_split (
@ S varchar (8000), -- string to be split @ split varchar (10) -- Data Separator) returns table

 

 

As
Return (
Select row_number () over (order by number) Rn,
Substring (@ s, number, charindex (@ split, @ [email protected], number)-number) as COL
From master .. spt_values
Where type = 'p' and number <= Len (@ s + 'A ')
And charindex (@ split, @ [email protected], number) = Number) Go
Select * From DBO. f_split ('11, 2, 3 ',',')
Select * From DBO. fn_splittsql ('11, 2, 3 ',', ') Go
-- Split multiple columns
If object_id ('fn _ mutisplittsql ') is not null drop function fn_mutisplittsql go
Create Function DBO. fn_mutisplittsql (@ s nvarchar (max), @ split nchar (1), @ [email protected] nchar (1) = n', ') returns table as return
Select * from (select D. RN, 'col' + Cast (n-len (replace (left (COL, n), @ [email protected], '') + 1 as varchar (10 )) as attribute,
Substring (COL, N, charindex (@ [email protected], Col + @ [email protected], n)-N) as Value
From (select N-len (replace (left (array, n), @ split, '') + 1 as RN, substring (array, N, charindex (@ split, array + @ split, n)-N) as col from (select @ s as array) as d join DBO. nums
On n <= Len (array)
And substring (@ split + array, N, 1) = @ split) as d join DBO. Nums A on n <= Len (COL)
And substring (@ [email protected] + Col, N, 1) = @ [email protected]) as D encode (max (value) for Attribute
In (col1, col2, col3, col4, col5) as P go
Select col1, col2, col3, col4 from DBO. fn_mutisplittsql

('$092-1350,099201-080901,12050720, 2012-6-11 $092-0970,099204-072301,12050734, 2012-6-11 $', '$', ',') Go