2

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.

Thunder
  • 10,366
  • 25
  • 84
  • 114

6 Answers6

3

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
  • 232,371
  • 49
  • 380
  • 404
  • @Andomar We could have any data in first column so could not hard-code it. – Thunder Jan 17 '10 at 15:51
  • 1
    @Thunder: The second part is just your example data in T-SQL; the query with `for xml path` should work for any data – Andomar Jan 17 '10 at 15:55
  • @Andomar I think its correct but I already have table with data ,can we link @t to an existing table? – Thunder Jan 17 '10 at 15:58
  • 1
    @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 Jan 17 '10 at 16:01
  • @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 Jan 17 '10 at 16:08
  • 1
    @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 Jan 17 '10 at 16:11
1

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

Community
  • 1
  • 1
Paul Dixon
  • 295,876
  • 54
  • 310
  • 348
  • using T-SQL not possible singe I am using the result in c# as a result of executescalar – Thunder Jan 17 '10 at 15:46
  • @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 Jan 17 '10 at 15:56
0

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
  • 4,714
  • 2
  • 27
  • 31
  • 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 Jan 17 '10 at 15:54
0

Use GROUP_CONCAT() with DISTINCT:

SELECT GROUP_CONCAT(DISTINCT colName) FROM tblName;
Crozin
  • 43,890
  • 13
  • 88
  • 135
0

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
  • 120,909
  • 25
  • 266
  • 342
0

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
  • 10,366
  • 25
  • 84
  • 114