sqlsql-servert-sql

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.


Solution

  • 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