how to select distinct and concatenate in sql

Question

Lets say we have a following table with two columns and following rows of data in SQLServer-2005:

Tiger    50
Wolf     4
Tiger    53
Lion     55
Elephant 54
Rhino    52
Lion     5

Can we have a sql query that result as following: Tiger,Wolf,Lion,Elephant,Rhino as a single string varchar output?Is it possible ? using T-SQL not possible singe I am using the result in c# as a result of executescalar Thank you in advance.

Solution 1

You can use for xml path to concatenate the values:

select distinct name + ', ' as [text()]
from @t
for xml path('')

-->
Elephant, Lion, Rhino, Tiger, Wolf, 

Chop of the last 2 bytes if you don't like trailing ,'s.

Not sure why you can't use T-SQL, you can use this in combination with ExecuteScalar() just fine.

Sample data:

declare @t table (name varchar(max), id int)
insert into @t 
select 'Tiger', 50
union all select 'Wolf', 4
union all select 'Tiger', 53
union all select 'Lion', 55
union all select 'Elephant', 54
union all select 'Rhino', 52
union all select 'Lion', 5

Solution 2

See How to return multiple values in one column (T-SQL)?

Comments

[Thunder]

using T-SQL not possible singe I am using the result in c# as a result of executescalar

[Thunder]

@Crozin I think your answer is near to solution but GROUP_CONCAT was not recognised ,Please note that we are using sqlserver2005

[Andomar]

Group_concat works in MySQL, but the question is about SQL Server

[Thunder]

@Andomar We could have any data in first column so could not hard-code it.

[Bob Palmer]

downvoting this because the example is in the wrong SQL dialect, and should not have been upvoted.

[Thunder]

Sored procedure could not be written as it would increase more process in deployment as we will have to add sproc to db manually

[Andomar]

@Thunder: The second part is just your example data in T-SQL; the query with for xml path should work for any data

[Aaronaught]

@Thunder, that doesn't make any sense, the only requirement for ExecuteScalar is that there is only one result. There is no restriction whatsoever on what the SQL looks like before the final SELECT. I'm upvoting this one.

[Thunder]

@Andomar I think its correct but I already have table with data ,can we link @t to an existing table?

[Andomar]

@Thunder: Yeah, @t is just the name of my table variable (it's not a real table but a table variable, so it starts with @.) You can replace @t with the name of your table

[Thunder]

@Andomar Thanks Andomar ,It worked ! alo quite amazed this is very new to me.I have never come across "for xml path('')" can u let us know what is this actually ,I am sure its not normal SQL

[Andomar]

@Thunder: It's a construct meant for generating XML output. To use it for generating comma-seperated lists is something of a trick; it wasn't designed for that. You can find more about the intended uses here: msdn.microsoft.com/en-us/library/ms190922.aspx

[Crozin]

Oh, my bad... I didn't noticed it's about sql-server-2005.