Multiple SQL methods to convert query results into strings

SQL overlay (TIPS): You can use SQL to convert the result set to a string.

As follows:

There can be at least three methods for conversion:

1. in. in NET, the common practice is to first retrieve the result set and put it in the DataTable, and then use the foreach loop to retrieve each field in series. in this case, the same method is also available in SQL, that is, using CURSOR for loop. The sample code is as follows:

The code is as follows:

Copy code

Cursor Declare @ mytable table (Col1 varchar (10) insert into @ mytable values ('A'), ('BB '), ('CC') select SUBSTRING (@ Colstring, 2, LEN (@ Colstring)-1) as Colstringdeclare mytype cursor local forselect Col1 from @ mytableopen mytypefetch next from mytype into @ Col1while @ FETCH_STATUS = 0 beginset @ Colstring + = ', '+ @ Col1fetch next from mytype into @ Col1enddeallocate mytypedeclare @ Colstring varchar (50) = ''declare @ Col1 varchar (10)

 

Converted string:

CURSOR is inefficient in SQL. When the data volume is large (> 1 Million), it will seriously affect the performance and is not recommended.

2. Assign values directly in SQL queries. This method is simple in CODE.

Code

The code is as follows:	Copy code
Declare @ mytable table (Col1 varchar (10) insert into @ mytable values ('A'), ('BB '), ('CC') declare @ Colstring varchar (50) select @ Colstring = isnull (@ Colstring + ',', '') + isnull (Col1,'') from @ mytable select @ Colstring as Colstring

 

Running result:

3. FOR XML PATH

First, convert it to the XML database type.

The code is as follows:	Copy code
Declare @ mytable table (Col1 varchar (10) insert into @ mytable values ('A'), ('BB '), ('CC') Select ', '+ Col1 -- No alias FROM @ mytable order by Col1 for xml path ('type ')

 

The input result is of the xml type and the TYPE is a node.

<TYPE>, AA </TYPE> <TYPE>, BB </TYPE> <TYPE>, CC </TYPE>

If there is no TYPE node, it is close to the desired result. Modify the CODE as follows:

Code

The code is as follows:	Copy code
Declare @ mytable table (Col1 varchar (10) insert into @ mytable values ('A'), ('BB '), ('CC') Select ', '+ Col1 -- No alias FROM @ mytable order by Col1 for xml path ('')

 

That's great. You only need to cancel the comma (,) and modify the CODE:

The code is as follows:	Copy code
FOR XML PATH Declare @ mytable table (Col1 varchar (10) insert into @ mytable values ('A'), ('BB '), ('CC') select STUFF (SELECT ', '+ Col1 -- No alias FROM @ mytable order by Col1 for xml path (''), 1, 1, space (0) as Colstring

 

Expected result:

The extension is as follows:

Code

The code is as follows:	Copy code
Declare @ mytable table (id int, potype varchar (10) insert into @ mytable values (1, 'a> A') insert into @ mytable values (1, 'B & B') insert into @ mytable values (1, 'C

 

Code

The code is as follows:	Copy code
DECLARE @ mytable TABLE (id INTEGERNOTNULL, potype VARCHAR (10) NOTNULL); INSERT @ mytable VALUES (1, 'a> A'); INSERT @ mytable VALUES (1, 'B & B'); INSERT @ mytable VALUES (1, 'C

The second or third method is recommended.