ansaurus

Question

how to select distinct and concatenate in sql

Answer 1

+1 A:

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

Paul Dixon 2010-01-17 15:45:04

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

Thunder 2010-01-17 15:46:48

@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.

Aaronaught 2010-01-17 15:56:34

Answer 2

+1 A:

Assuming you are getting this from ExecuteScalar and using the resulting composite string in C#, then you are going to end up writing a sproc, or just having your code work with the datatable.

Can you provide more detail on why you can't use a sproc? A few good examples have been provided that would do fine with that, and you can still get your scalar result

Bob Palmer 2010-01-17 15:45:08

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

Thunder 2010-01-17 15:54:22

Answer 3

A:

Use GROUP_CONCAT() with DISTINCT:

SELECT GROUP_CONCAT(DISTINCT colName) FROM tblName;

Crozin 2010-01-17 15:46:13

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

Thunder 2010-01-17 15:48:49

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

Andomar 2010-01-17 15:49:03

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

Bob Palmer 2010-01-17 15:52:26

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

Crozin 2010-01-17 16:11:47

Answer 4

+3 A:

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

Andomar 2010-01-17 15:48:43

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

Thunder 2010-01-17 15:51:51

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

Andomar 2010-01-17 15:55:08

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

Thunder 2010-01-17 15:58:01

@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

Andomar 2010-01-17 16:01:42

@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

Thunder 2010-01-17 16:08:56

@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: http://msdn.microsoft.com/en-us/library/ms190922.aspx

Andomar 2010-01-17 16:11:00

Answer 5

A:

The most efficient way is probably to use a User-Defined Aggregate, but you can also abuse the UPDATE statement:

DECLARE @Result varchar(500)
SET @Result = ''

UPDATE Animals
    SET @Result = @Result + Name + ','

SELECT @Result

Aaronaught 2010-01-17 15:51:57

Answer 6

+1 A:

This is how Andomar's Answer was implemented.Thanks to Andomar's answer got to learn some thing new with this .

select distinct summaryColumn + ', ' as [text()] from tablename for xml path('')

Thunder 2010-01-17 16:11:46

ansaurus

tags:

views:

answers:

how to select distinct and concatenate in sql

related questions