This query concatenates all the values found in the name column of the sys.all_columns table for a specific table.

The coalesce function is used to take into account that the very first element doesn't need to be preceded with a comma.

In the first step, @listStr is declared as a string variable, but it is not initialized. Therefore it has the value NULL.

You can consider the SELECT statement as some sort of loop, which goes over each row of the sys.all_columns table and takes the current name in each iteration and concatenates it to the string variable. For the first row, @listStr is still NULL, so COALESCE returns the empty string, giving the result @listStr = 'column1.

For the second row, @listStr is not NULL anymore, so now you get the result @listStr = 'column1', 'column2.

In each iteration, the current column is appended to the string in the following form: + ','columnX