SQL Join and concatenate rows

Question

I have a many-to-many relationship between two tables.

Table God_Restaurants contains my restaurants.

Table God_RestaurantKat contains the different categories.

Table God_RestKatReference contains two columns each holding the id of the two tables.

The follwing statement is what I can come up with, but does not give me the output that i want.

DECLARE @Names VARCHAR(8000) 
SELECT DISTINCT R.RestaurantID as Restaurantid, 
                R.RestaurantName as Restaurantname, 
                K.RestaurantKatName as RestKatName 
FROM God_Restaurants R 
LEFT JOIN God_RestKatReference as GodR ON R.RestaurantId = Godr.RestaurantId 
LEFT JOIN God_RestaurantKat as K ON GodR.RestaurantKatId = K.RestaurantKatId 
WHERE R.RestaurantPostal = 7800

I would like the output to be informations about the restaurant and in the last column, a concatenated row of categories.

I'm pretty sure there's a duplicate question or three somewhere on SO, but I can't find them right now. So, for starters look at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f09d4166-2030-41fe-b86e-392fbc94db53/ — lc., Oct 17 '12 at 18:27
possible duplicate of [Simulating group\_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) Found it. And look at [this answer](http://stackoverflow.com/a/5981860/44853) — lc., Oct 17 '12 at 18:30

Roman Pekar · Accepted Answer · 2012-10-18T08:47:06.163

2

To concatenate values you can use for xml path(''). There are wrong xml path solutions, you should use value and type for special characters.

declare @Temp table (id int, Name nvarchar(max))
declare @date datetime
declare @i int

insert into @Temp
select 1, 'asasd' union all
select 1, 'sdsdf' union all
select 2, 'asdad' union all
select 3, 'asd<a?>&sdasasd' union all
select 3, 'fdgdfg'

select @i = 1
while @i < 9
begin
    insert into @Temp
    select id, Name from @Temp

    select @i = @i + 1
end

select count(*) from @Temp

select @date = getdate()

select
    A.id,
    stuff((select ', ' + TT.Name from @Temp as TT where TT.id = A.id for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') as Names
from @Temp as A
group by A.id

select datediff(ms, @date, getdate())

select @date = getdate()

select distinct
    A.id,
    stuff((select ', ' + TT.Name from @Temp as TT where TT.id = A.id for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') as Names
from @Temp as A

select datediff(ms, @date, getdate())

You can also use variable solution

declare @temp nvarchar(max)

select @temp = isnull(@temp + ', ', '') + str
from (select '1' as str union select '2' as str union select '3' as str) as A

select @temp

edited Oct 18 '12 at 08:47

answered Oct 17 '12 at 18:32

Roman Pekar

107,110
28
195
197

A better solution for your first query would be to use `group by id` instead of `distinct`. With `distinct` the sub query is executed 5 times and with `group by` it will be executed 3 times with this sample data. With larger datasets you will notice a difference in performance. – Mikael Eriksson Oct 18 '12 at 08:14
yes, but it also needs some kind of `min` over concatenated fields. I think best solution is to get `distinct` ids in subquery and then get concatenated data – Roman Pekar Oct 18 '12 at 08:27
1

No it does not need `min`. The distinct version will not only do distinct on ids it will do distinct on the combination of the id and the concatenated string. – Mikael Eriksson Oct 18 '12 at 08:31
1

yes, you're right, it's not good to use `distinct` in such a query, and you don't need an aggregate on concatenated fields. I've changed a query, added counting of milliseconds for query. For 1280 rows and 3 ids `group by` executes for 3 ms, and `distinct` for about 3000. So if you have small number of ids and large number of rows you can really notice a difference in performance. Thank you – Roman Pekar Oct 18 '12 at 08:45

SQL Join and concatenate rows

1 Answers1

Linked