SQL Server : return multiple values from table lookup from itself

Question

In SQL Server I want to lookup all [Site] values for each row's following [Year], looking up the unique [ID]. However, some rows have multiple [Site] values for the next [Year]. I want to produce the following [Destination] column:

[ID]    [Year]    [Site]    [Destination]
------------------------------------------
A       2012      London    London, LA
A       2013      London    
A       2013      LA        
B       2012      Paris     Berlin, Madrid
B       2012      Paris     Berlin, Madrid
B       2013      Berlin    
B       2013      Madrid

I have accomplished this in Powerpivot DAX with:

Destination = 
CONCATENATEX(
    FILTER(Table,
           [Year] = EARLIER([Year]) + 1 &&
           [ID] = EARLIER([ID])
    Table[Site], ", ")

But I have no idea how best to do this in SQL. Any ideas greatly appreciated, thanks for your time.

FYI Management studio is only a client interface - its not the database which is just SQL Server. — Dale K, May 19 '20 at 23:18

Marko Radivojević · Accepted Answer · 2020-05-20T14:05:10.300

In case the table is named Travel, you could do it like this:

SELECT
    ID,
    Year,
    Site,
    STRING_AGG(Destination, ', ') AS Destination
INTO #Destination
FROM
(
    SELECT DISTINCT
        t1.ID,
        t1.Year,
        t1.Site,
        t2.Site AS Destination
    FROM Travel t1
        INNER JOIN Travel t2 ON t2.Year = t1.Year + 1 AND t2.ID = t1.ID
) AS Dst
GROUP BY
    ID,
    Year,
    Site

SELECT
    t.*,
    d.Destination
FROM Travel t
    LEFT JOIN #Destination d ON t.ID = d.ID AND t.Site = d.Site AND t.Year = d.Year

Explanation:

For every ID, Year and Site take all sites (Destinatins) in the next year, without repeating data.

SELECT DISTINCT
    t1.ID,
    t1.Year,
    t1.Site,
    t2.Site AS Destination
FROM Travel t1
    INNER JOIN Travel t2 ON t2.Year = t1.Year + 1 AND t2.ID = t1.ID

Store that data into temporary table #Destination which contains ID, Year, Site and all destinations in a single string, separated by ,.

SELECT
    ID,
    Year,
    Site,
    STRING_AGG(Destination, ', ') AS Destination
INTO #Destination
FROM
(
    -- previous code
) AS Dst
GROUP BY
    ID,
    Year,
    Site

Finally, take destination for each site.

SELECT
    t.*,
    d.Destination
FROM Travel t
    LEFT JOIN #Destination d ON t.ID = d.ID AND t.Site = d.Site AND t.Year = d.Year

Edit: In case of SQL Server 2016 or older.

SELECT DISTINCT
    ID,
    Year,
    Site,
    SUBSTRING((SELECT ', ' + Site FROM Travel WHERE Year = t.Year + 1 AND ID = t.ID FOR XML PATH('')), 3, 1000) AS Destination
INTO #Destination
     FROM Travel t

SELECT
    t.*,
    d.Destination
FROM Travel t
    LEFT JOIN #Destination d ON t.ID = d.ID AND t.Site = d.Site AND t.Year = d.Year

Explanation:

This example is using XML PATH. STUFF is omitted as it can cause slowdown in performance. Temporary table #Destination is simpler to create. Main change is:

SUBSTRING((SELECT ', ' + Site FROM Travel WHERE Year = t.Year + 1 AND ID = t.ID FOR XML PATH('')), 3, 1000) AS Destination

1.

SELECT ', ' + Site FROM Travel WHERE Year = t.Year + 1 AND ID = t.ID

This line of code takes all Sites for given ID and next Year.

2.

SELECT ', ' + Site FROM Travel WHERE Year = t.Year + 1 AND ID = t.ID FOR XML PATH('')

This line adds FOR XML PATH(''). It creates a single string containing Sites for given ID and next Year. In front of every Site are , and empty space.

3.

SUBSTRING((SELECT ', ' + Site FROM Travel WHERE Year = t.Year + 1 AND ID = t.ID FOR XML PATH('')), 3, 1000) AS Destination

This line of code adds SUBSTRING( before and , 3, 1000) after line in previous step, and names it Destination. Purpose of SUBSTRING is to remove first , and empty space, because they exist in front of every Site. It does so by removing all characters before the third one (so first - ,, and second - empty space) and all characters after the 1000th character. It can be any number instead of 1000 as long as it is bigger than length of Destination string.

Great answer thanks a lot. Unfortunately I'm using SQL Server 2016 and I'm getting the error: **'STRING_AGG' is not a recognized built-in function name**. Is there an equivalent function I can use on that line? I'm trying the "stuff" function from here but I can't get it to work: https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-pre-2017 — Gabriel H, May 20 '20 at 12:12
Hi @GabrielH. I've added a solution in case of *SQL Server 2016 or older*. I've used example from link you've provided. My bad as I supposed it might not work because of `STRING_AGG`. I am glad you accepted the answer anyway and I hope this time it will work correctly. — Marko Radivojević, May 20 '20 at 14:07

SQL Server : return multiple values from table lookup from itself

1 Answers1