Sql: How to combine multiple rows into a string as an expression within an update

Question

In sql (MS sql server specifically) is it possible to combine multiple rows into a single string as an expression which is itself part of an update that is being applied to multiple rows. I have come across the approaches of using COALESCE or FOR XML PATH (e.g. How to get multiple rows into one line as a string? ) but can't get them to work in my more complex case with the extra dimension of 'listiness'.

My problem boils down to, in words:

A Project has some Launches. A Launch has a LaunchType and a date. I have a big output table of projects ProjectOutput and I want to update a column in it with a CSV string of all the launch type names for that project that happen in the same month as the first (chronologically) launch of that project.

In sql:

UPDATE ProjectOutput
SET LaunchNamesColumn = <INSERT MAGICAL SQL STRING CONCATTING ACROSS ROWS FUNCTION HERE> of Launch.name
FROM ProjectOuput
INNER JOIN Launch ON Launch.projectId = ProjectOutput.projectId
INNER JOIN LaunchType AS lt ON LaunchType.launchTypeId = Launch.launchTypeId
OUTER APPLY (
  SELECT TOP 1 Launch.month, Launch.year
  FROM Launch
  INNER JOIN Project ON Project.projectId = Launch.projectId
  WHERE Project.projectId = ProjectOutput.projectId
  --In reality there's loads more JOINS and WHERE conditions here
  ORDER BY Launch.date
) firstLaunch
WHERE Launch.month = firstLaunch.month AND Launch.year = firstLaunch.year

If there were only 1 Launch per Project then the stuff would not be needed and just

SET LaunchNameColumn = Launch.name

However as there can be several Launches per Project some operation is needed to join them. I tried:

SET LaunchNamesColumn = STUFF((SELECT ', ' + lt.name FROM lt FOR XML PATH('')), 1, 2, '')

However that doesn't work (error, invalid name) because it doesn't know what the alias lt is inside that SELECT. If you just say LaunchType or dbo.LaunchType then the query runs but then you are just looping over all the possible launch types rather than only those returned by the big query below. What I really want is for that FROM in the SELECT FOR XML PATH is to be the result set of the giant query below (whereas in all the examples I've found so far it's just a simple table), but copying and pasting that in seems so wrong. Maybe there is some mental block or sql feature I'm unaware of that would make this work, or is it not possible?

score 1 · Answer 1 · answered Jun 10 '15 at 17:43

It's a little bit difficult to understand your SQL without description of the tables, but what you should do is have the query with the XML path so that it returns only those items that you want to be concatenated for that single row, so my guess is that you want actually something like this:

UPDATE O
SET LaunchNamesColumn = STUFF((SELECT ', ' + lt.Name
  From Launch L
  INNER JOIN Launch L ON L.projectId = O.projectId
  INNER JOIN LaunchType AS lt ON lt.launchTypeId = L.launchTypeId
  WHERE L.month = FL.month AND L.year = FL.year
  FOR XML PATH('')), 1, 2, '') 
FROM ProjectOutput O
CROSS APPLY (
  SELECT TOP 1 L2.month, L2.year
  FROM Launch L2
  WHERE L2.projectId = O.projectId
  -- Removed the other tables from here. Are they really needed?
  ORDER BY L2.date
) FL

Couldn't really test this, but hopefully this helps.

score 1 · Accepted Answer · answered Jun 10 '15 at 17:52

The problem you have is that in the SET stage of your query you only have access to one of the matching Launches as there is no grouping applied.

You can achieve want you want by moving your Launch lookup into a sub-query over the ProjectOutput rows. A simplified example:

UPDATE ProjectOutput
SET LaunchNamesColumn = STUFF((
        SELECT ', ' + Launch.name 
        FROM Launch
        -- OUTER APPLY is not required within the sub-query.
        INNER JOIN (
          SELECT TOP 1 Launch.month, Launch.year
          FROM Launch
          -- Filter results to specific project.
          WHERE Launch.projectId = ProjectOutput.projectId
          ORDER BY Launch.date
        ) firstLaunch ON Launch.month = firstLaunch.month AND Launch.year = firstLaunch.year 
        -- Filter results to specific project.
        WHERE Launch.projectId = ProjectOutput.projectId
        FOR XML PATH('')
    ), 1, 2, '')
FROM ProjectOutput

Logically the sub query is run once per ProjectOutput record, allowing you to filter and group by each ProjectId.

Also nice bit of syntax that may simplify your query is SELECT TOP WITH TIES,

UPDATE ProjectOutput
SET LaunchNamesColumn = STUFF((
        SELECT TOP (1) WITH TIES ', ' + Launch.name 
        FROM Launch
        WHERE Launch.projectId = ProjectOutput.projectId
        ORDER BY Launch.Year, Launch.Month
        FOR XML PATH('')
    ), 1, 2, '')
FROM ProjectOutput

This will return all the matching Launches that have the lowest Year then Month value.

As this and other answers said, they key thing I needed to do was move the SELECTing of the launches and their launch types into the FROM that is with the FOR XML PATH. The reason I hadn't done that is the SETing of the column in ProjectOutput is actually a SELECT CASE and sometimes we just want to set the date itself and I wanted to be able to re-use the great big find first launch query I had written rather than copy-pasting. But using that nifty WITH TIES tip it's not so big anymore and I've just split the cases into 2 separate updates that both work now. Thanks. — Uberdude, Jun 11 '15 at 09:59

score 0 · Answer 3 · answered Jun 10 '15 at 17:43

Can you add the Launch and LaunchType tables into your STUFF and filter it based on the Project table or Launch table in the main query?

STUFF((SELECT ', ' + lt.name 
        FROM Launch l 
             JOIN LaunchType lt2 ON lt2.launchTypeId  = l.launchTypeId 
        WHERE
            l.projectId  = Launch.projectId
        FOR XML PATH('')), 1, 2, '')

Or you could maybe create a CTE and select all of the launches then use your Stuff statement on the CTE

WITH cteLaunch AS (
    SELECT  l.projectId,
            lt.NAME
    FROM    Launch ON Launch.projectId = ProjectOutput.projectId
            INNER JOIN LaunchType AS lt ON LaunchType.launchTypeId = Launch.launchTypeId
            OUTER APPLY (SELECT TOP 1
                            Launch.month,
                            Launch.year
                         FROM
                            Launch
                            INNER JOIN Project ON Project.projectId = Launch.projectId
                         WHERE
                            Project.projectId = ProjectOutput.projectId
                         ORDER BY Launch.date
                ) firstLaunch
    WHERE   Launch.month = firstLaunch.month
            AND Launch.year = firstLaunch.year            
)
UPDATE
    ProjectOutput
SET 
    LaunchNamesColumn = STUFF((SELECT ', ' + lt.name 
                                FROM cteLaunch cte 
                                WHERE cte.projectId = ProjectOuput.projectId
                                FOR XML PATH('')), 1, 2, '')
FROM
    ProjectOuput
    INNER JOIN cteLaunch ON cteLaunch.projectId = ProjectOutput.projectId

score 0 · Answer 4 · answered Jun 10 '15 at 17:52

0

I think you are really close; it's the alias getting in the way:

SET LaunchNamesColumn = STUFF((SELECT ', ' + lt.name 
FROM LaunchType AS lt 
WHERE lt.launchTypeId = Launch.launchTypeId FOR XML PATH('')), 1, 2, '')

answered Jun 10 '15 at 17:52

SQLHound

546
11
24

Sql: How to combine multiple rows into a string as an expression within an update

4 Answers4