SQL Script to Concatenate column values


There is frequent need for sql string concatenation to get a list of column values(Comma separated value) of a table. Generally udf's (Used Defined Function) are used to concatenate string in sql. Here is a very simple T-SQL sample using FOR XML PATH() in order concatenate strings values in SQL SERVER



There is frequent need for sql string concatenation to get a list of column values(Comma separated value) of a table.
Generally udf's (Used Defined Function) are used to concatenate string in sql.
Here is a very simple T-SQL sample using FOR XML PATH() in order concatenate strings values in SQL SERVER


-- Consider follwing User Table
CREATE TABLE UserTable
(
UserID INT,
UName VARCHAR(100)
)

-- Insert Few users in UserTable
INSERT INTO UserTable VALUES
(1,'DOT')
,(2,'NET')
,(3,'S')
,(4,'P')
,(5,'I')
,(6,'D')
,(7,'E')
,(8,'R')

-- Now let us try to Concatenate all the values in UName Column From UserTable Using FOR XML PATH
SELECT
STUFF(
(
SELECT ',' + A.UNAME
FROM UserTable A
FOR XML PATH('')
), 1, 1, '') AS CONCATENATEDSTRING
--In the above STUFF function is used to replace the first character in the string values with an empty string value.


SQL Concatenation can also be done using COALESCE as shown below




DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr, '') + UName
FROM UserTable
SELECT @listStr AS 'Concatenated String'

-- Append Comma in order to get Concatenated String as comma separated Values
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr + ',' , '') + UName
FROM UserTable
SELECT @listStr AS 'Concatenated String'


Related Articles

How to get rows into comma separated values column in sql

How to get rows into comma separated values column in sql. Fetching rows values into comma separated column value is required lots of time in our projects. So I tried to achieve this and lastly found one solution. Use this to get all row values into one single column value with comma separation.

Split Comma separated values into Columns

In this article, I explain about how to split a string with delimiter into list of rows. De limiter can be comma or plus our requirement is to convert a single row of a table into multiple rows based on number of delimiter.

More articles: SQL concatenation SQL concatenate values Comma separated value

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: