Concatenate SQL query result string output

In SQL queries, if we want to output the queried result set as a string, for example, querying employees in the northwind database, the returned result is {address | birthdate | city | country | employeeid | extension | firstname | hiredate | homephone | lastname | notes | photo | photopath | postalcode | region | reportsto | title | titleofcourtesy |} format.

We can do this.

 

Declare @ columns nvarchar (4000) <br/> set @ columns = ''<br/> select @ columns = @ columns + name + '|' from syscolumns where id = object_id ('table name ') <br/> Print @ columns <br/>

The previous variable @ columns is assigned a null value {set @ columns = ''}. Why should I assign a value to @ columns? In SQL (SQL server2005), the default value of declaring a variable (if not initialized) is null, and null is an unknown value, which is different from the null. the result of adding a null value to all values is null, which shows why we need to assign a value to @ columns first.

Of course, we can also write it like this.

 

Declare @ columns nvarchar (4000) <br/> select @ columns = isnull (@ columns ,'') + name + '|' from syscolumns where id = object_id ('table name') <br/> Print @ Columns

The same is true. When assigning values to @ columns, check whether it is a null value and replace it with a null value.