CODEWITHSUNDEEP
sqlsql-servert-sql
K N
K N

Reputation: 343

SQL Join and concatenate rows

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.

Upvotes: 1

Views: 3801

Answers (1)

roman
roman

Reputation: 117626

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

Upvotes: 2

Related Questions