Now I will explain how to write a query to concatenate row values in SQL
Server.
Here I will explain with one example for that I have one table UserDetails like as shown below
 |
Now I want bind the username row values into single
row like as shown below
 |
If
we want to concatenate one column row values into single row we need to write
query like this
DECLARE @name NVARCHAR(MAX)
SELECT @name = COALESCE(@name + ',', '') + UserName FROM UserDetails
SELECT UserNames =
@name
|
If
you observe above query I used function COALESCE it returns the first non-null expression
among its arguments.
OutPut
|
We can concatenate rows in another way also
Another
way
If
we want to concatenate rows of
column we can write query like as shown below
DECLARE @name NVARCHAR(MAX)
SET @name=''
SELECT @name =
@name +','+UserName FROM UserDetails
SET @name = Substring(@name, 2, (len(@name)))
SELECT @name as
UserNames
|
OutPut
|
ReplyDeleteawsome piece of information, I had come to know about your website from my friend vinod, indore,i have read atleast seven posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, Single Row Function in sql
awful piece of information, I had come to know about your blog from my friend vimal, mumbai,i have read atleast 13 posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, Single Row Function
ReplyDelete