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