SQL Server places all the values of a column in a select result set in a single field, separated by commas

First we have a table query result as follows:

Now we want to put the contents of the User_name column in a field:

A row of SQL statements resolves the issue:

Select  STUFF ((select  ', ' +convert (VARCHAR, user_name) from Te_user for XML PATH (")), 1, 1, ') as UserName

Explain the above statement:

1, first stuff function in the above statement is to remove the concatenation of the front of the string comma

The use of the stuff function is probably stuff (param1, startIndex, Length, param2), which means to remove the length characters from param1 (in SQL from 1 rather than 0) Then replace the deleted characters with param2. Please use your own Baidu

2, focus on the for XML PATH:

Executes the result of the SELECT * from Te_user for XML path statement:

<row>  <USER_ID>1</USER_ID>  <USER_NAME> Zhang San </USER_NAME>  <user_ Password>c4ca4238a0b923820dcc509a6f75849b</user_password></row><row>  <USER_ID>2 </USER_ID>  <USER_NAME> John Doe </USER_NAME>  <USER_PASSWORD> C4ca4238a0b923820dcc509a6f75849b</user_password></row><row>  <USER_ID>3</USER_ID >  <USER_NAME> Harry </USER_NAME>  <user_password>c4ca4238a0b923820dcc509a6f75849b</ User_password></row>

It can be seen that the results of SQL execution are converted to XML format.

Let's do this again. The following SQL SELECT * from Te_user for XML PATH (' ABC ')

<ABC>  <USER_ID>1</USER_ID>  <USER_NAME> Zhang San </USER_NAME>  <user_ Password>c4ca4238a0b923820dcc509a6f75849b</user_password></abc><abc>  <USER_ID>2 </USER_ID>  <USER_NAME> John Doe </USER_NAME>  <USER_PASSWORD> C4ca4238a0b923820dcc509a6f75849b</user_password></abc><abc>  <USER_ID>3</USER_ID >  <USER_NAME> Harry </USER_NAME>  <user_password>c4ca4238a0b923820dcc509a6f75849b</ User_password></abc>

The row node of the XML is found to be our custom ABC, so if we use the FOR XML PATH ("), it means the node becomes empty:

<USER_ID>1</USER_ID><USER_NAME> Zhang San </USER_NAME><USER_PASSWORD> c4ca4238a0b923820dcc509a6f75849b</user_password><user_id>2</user_id><user_name> John Doe </ user_name><user_password>c4ca4238a0b923820dcc509a6f75849b</user_password><user_id>3</ User_id><user_name> Harry </user_name><user_password>c4ca4238a0b923820dcc509a6f75849b</user _password>

After the node has been emptied we execute select ', ' +convert (VARCHAR, user_name) from the Te_user for XML PATH (') statement result:

, Zhang San, John Doe, Harry

This realizes the value of the User_name column is removed to join together and then use the stuff function we said above to remove the first comma.

Above.

SQL Server places all the values of a column in a select result set in a single field, separated by commas