CODEWITHSUNDEEP
sql-servert-sql
Jan Šalomoun
Jan Šalomoun

Reputation: 135

Joining inside subqueries - STUFF

Please could you help me. I do something wrong. 

SELECT        
    EventSpotsJoin.Event, Links.LongLat 
FROM 
    EventSpotsJoin 
INNER JOIN 
    Links ON EventSpotsJoin.Spot = Links.IdLinks 
ORDER BY 
    EventSpotsJoin.Event

Result is

2054    39.0440182, -74.7659984
2054    28.29555, -80.60898333333333
2068    39.0440182, -74.7659984
2068    28.29555, -80.60898333333333

And I would like to join lat long into one row. I use this code.

 SELECT 
     [EventSpotsJoin].[Event], 
     STUFF((SELECT '|' + CAST(Links.LongLat AS nvarchar) 
            FROM [dbo].[EventSpotsJoin]
            WHERE EventSpotsJoin.Spot = Links.IdLinks
            FOR XML PATH ('')), 1, 1, '')
FROM            
    EventSpotsJoin 
INNER JOIN
    Links ON EventSpotsJoin.Spot = Links.IdLinks
ORDER BY 
    EventSpotsJoin.Event

and the result is wrong:

2054    39.0440182, -74.7659984|39.0440182, -74.7659984
2054    28.29555, -80.60898333333333|28.29555, -80.60898333333333
2068    39.0440182, -74.7659984|39.0440182, -74.7659984
2068    28.29555, -80.60898333333333|28.29555, -80.60898333333333

I need to group it by Event and join not the same lat long result.

Upvotes: 2

Views: 57

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this : Assuming That you are expecting out put for event id 2054 is 39.0440182, -74.7659984|28.29555, -80.60898333333333

SELECT [EventSpotsJoin].[Event], STUFF(
     (SELECT '|' + CAST(Links.LongLat AS nvarchar) 
      FROM Links 
      WHERE EventSpotsJoin.Spot = Links.IdLinks
      FOR XML PATH (''))
     , 1, 1, '')
FROM EventSpotsJoin 
ORDER BY EventSpotsJoin.Event

Upvotes: 2

Related Questions