Group by for string concatenation 1

[CodingIsFun]

Hi all experts,

I am trying to concatenate strings within the group by statement:

select product,name
from product
group by product,name

normally this particular case would list all the unique product and name combinations

I would like to concatenate the name part of this:

lets say here are my records:

product1, name1
product1, name2
product1, name3
product2, name4
product2, name5

result set I would like:

product1, name1 + name2 + name3
product2, name4 + name5

in this particular case product and name fields are varchars..

I know I can build a user defined function to concatenate a variable then return that, but I would prefer to use direct SQL. Is this possible..

Thanks in advance..

 

[NoCoolHandle]

Group by needs to match your select list (or be the result of an agregate)

ie

Select [blue]FirstName + ' ' + LastName[/blue]
From northwind.dbo.Employees
Group By [blue]FirstName + ' ' + LastName[/blue]

works


HTH

Rob

 

[CodingIsFun]

It's not that simple..

I want to be able to concatenate rows into one record. Like how you would sum or count a number field using a group by aggregation. Instead I would like to concatenate the strings with the same group field.

like this:

select product, name
from test

returns:
product name
-------- ------
product1 name1
product1 name2
product1 name3
product2 name4
product2 name5

5 records

now if name was a numeric field and the values were not name<n> instead just <n>

like so:
product name
-------- ------
product1 1
product1 2
product1 3
product2 4
product2 5

the following would return 2 records

select product, sum(name)
from test
group by product

result:
product name
------- -----
product1 6
product2 9

but the field name returns a string so the function sum will not work..


Going back to the original records set
product name
-------- ------
product1 name1
product1 name2
product1 name3
product2 name4
product2 name5

????
select product, name
from test
group by product
????

The desired result for what I need should be 2 records with the fields concatenated together like so:

Results:

product name
------- -----
product1 name1 name2 name3
product2 name4 name5




I can do this with a user defined function but, the query is way too slow:

i.e.
CREATE function dbo.udf_test_name_ret(@product varchar(512))
returns varchar(8000)

AS
BEGIN

Declare @concat varchar(8000)
set @concat = ''

select @concat = @concat + ' ' + [name]
from test with (nolock)
where product = @product

return @concat
END
go

select distinct product,dbo.udf_test_name_ret(product)
from test with (nolock)


Is that more clear.

once again, thanks for the help...

 

[SQLDenis]

what's the max number of distinct values that name can have per product? 2,3 a lot more?
if it's 3 or less you can do a triple left join

let me know

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CodingIsFun]

could anywhere from 0 to n

 

[SQLDenis]

what about a #temp table
insert, update and select...has to be faster than the UDF

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CodingIsFun]

How do I make this work with a temp table? Could you elaborate a little please.

thanks

 

[SQLDenis]

example, I recreated you test data here

create table testMulti(product varchar(50),name varchar(50))

insert into testMulti
select 'product1',  'name1' union all
select 'product1',  'name2' union all
select 'product1',  'name3' union all
select 'product2',  'name4' union all
select 'product2',  'name5' 

--select * from testMulti

create table #temp (id int identity,product varchar(50),name varchar(50))
insert into #temp
select distinct product,null from testMulti

--select * from #temp
declare @maxId int, @LoopId int
declare @ProdVal varchar(50),@NameVal varchar(50)
select @maxId = max(id) from #temp
select @LoopId =1
while @LoopId <=@maxId
begin
	select @NameVal = ''
	select @ProdVal = product from #temp where ID =@LoopId
	
	select @NameVal =@NameVal + name + ',' from testMulti
	where product =@ProdVal
	
	update #temp set name =left(@NameVal,len(@NameVal)-1)
	where id =@LoopId
	
	set @LoopId = @LoopId + 1
end
select * from #temp

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[CodingIsFun]

Yes that will work, you get a star.

I'm wondering if there is a better way.. Maybe I could build an extension to do this inline instead of using a loop.

hmm.

Well, thanks again..

 

[SQLDenis]

I don't know if there is a better/faster way of doing this without looping since you have to concatenate values from different rows

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[amrita418]

Try running the udf solution with the following query instead:

select    product, 
          dbo.udf_test_name_ret(product)
from      test 
group by  product

Regards,
AA