What is the meaning of SELECT … FOR XML PATH(‘ ‘),1,1)?

by

There’s no real technique to learn here. It’s just a cute trick to concatenate multiple rows of data into a single string. It’s more a quirky use of a feature than an intended use of the XML formatting feature.

SELECT ',' + ColumnName ... FOR XML PATH('')

generates a set of comma separated values, based on combining multiple rows of data from the ColumnName column. It will produce a value like ,abc,def,ghi,jkl.

STUFF(...,1,1,'')

Is then used to remove the leading comma that the previous trick generated, see STUFF for details about its parameters.

(Strangely, a lot of people tend to refer to this method of generating a comma separated set of values as “the STUFF method” despite the STUFF only being responsible for a final bit of trimming)

More Related Contents:

  1. How do I obtain a Query Execution Plan in SQL Server?
  2. sql query to return differences between two tables
  3. Generate SQL Create Scripts for existing tables with Query
  4. SQL Server 2005 How Create a Unique Constraint?
  5. SQL Server: Make all UPPER case to Proper Case/Title Case
  6. How do I delete from multiple tables using INNER JOIN in SQL server
  7. Does Foreign Key improve query performance?
  8. What is a ‘multi-part identifier’ and why can’t it be bound?
  9. SQL Server convert string to datetime
  10. sql server select first row from a group
  11. CASE WHEN statement for ORDER BY clause
  12. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  13. Return number of rows affected by UPDATE statements
  14. CTE error: “Types don’t match between the anchor and the recursive part”
  15. Automate Version number Retrieval from .Dtsx files
  16. Execute Dynamic Query with go in sql
  17. TransactSQL to run another TransactSQL script
  18. How do I remove extended ASCII characters from a string in T-SQL?
  19. SQL server identity column values start at 0 instead of 1
  20. The text, ntext, and image data > types cannot be compared or sorted, except when using IS NULL or LIKE > operator
  21. Comma Separated values with SQL Query
  22. Using SQL Server as a DB queue with multiple clients
  23. Database Naming Conventions by Microsoft?
  24. Count records for every month in a year
  25. Is there a severe performance hit for using Foreign Keys in SQL Server?
  26. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  27. Backup SQL Schema Only?
  28. Date serial in SQL?
  29. SQL Server: +(unary) operator on non-numeric Strings
  30. Parameterise table name in .NET/SQL?

Leave a Comment