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

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?

--------------Solutions-------------

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.

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.

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

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, '')

Category:sql Time:2018-11-08 Views:0
Tags: sql sql server

Related post

  • How to combine multiple rows into one with nulled values where row values differ 2011-12-23

    How can I do with SQL Server to get a single row where the only non-null values are the ones that are consistent and non-null through all the selected rows. A B C D 10 NULL text NULL 4 abc text NULL 4 def text NULL Should give the following row: A B

  • Oracle combine multiple rows into one with distinct title 2011-09-15

    I have this statement that combines multiple rows into one and then outputs it in the way I need the data: SELECT COURSES_ID, REQUISITE_TYPE_TITLE , RTRIM ( xmlagg (xmlelement (c, CONDITION_TITLE || '' || REQ_TEXT || '' ) order by ORDER_NUM).extract

  • Combine multiple rows into one cell 2014-04-17

    Hi I was just wondering if there was an easy way to combine multiple rows into once cell without having to add them manually? I have the following formula =(A1&A2&A3) but the A's currently go down to around 500. Any help/advice appreciated. C

  • How can I combine multiple rows into one row? 2012-05-03

    So I have looked all day to find a good solution to this problem, and have come up empty. Let's say I have a table similar to this: ProjectID Position BilledSoFar NotBilled ---------------------------------------------------- EE123 AA1 100 200 EE456

  • How can I combine multiple rows into a comma-delimited list in Oracle? 2009-01-22

    This question already has an answer here: SQL Query to concatenate column values from multiple rows in Oracle 8 answers I have a simple query: select * from countries with the following results: country_name ------------ Albania Andorra Antigua .....

  • T-SQL Combine Multiple Rows Into Single Row 2010-12-16

    I have this T-SQL (simplified): select 3.00 as score1, null as score2, null as score3, null as score4 union all select null as score1, 4.5 as score2, 1.5 as score3, null as score4 Which produces this: score1 score2 score3 score4 ---------------------

  • How do I write a LINQ query to combine multiple rows into one row? 2012-02-24

    I have one table, 'a', with id and timestamp. Another table, 'b', has N multiple rows referring to id, and each row has 'type', and "some other data". I want a LINQ query to produce a single row with id, timestamp, and "some other data" x N. Like thi

  • Combining multiple rows into one row 2010-07-30

    I have a table containing user-account permissions and I'm trying to write a query to return one row for each user-account combination. Here is what I have. CltKey AcctKey TranTypeID Access 10 2499 10 0 10 2499 11 1 10 2499 12 1 10 2764 10 1 10 2764

  • How to combine multiple PNGs into one big PNG file? 2010-10-13

    I have approx. 6000 PNG files (256*256 pixels) and want to combine them into a big PNG holding all of them programmatically. What's the best/fastest way to do that? (The purpose is printing on paper, so using some web-technology is not an option and

  • Is there an Oracle SQL query that aggregates multiple rows into one row? 2009-07-13

    This question already has an answer here: SQL Query to concatenate column values from multiple rows in Oracle 8 answers I have a table that looks like this: A 1 A 2 B 1 B 2 And I want to produce a result set that looks like this: A 1 2 B 1 2 Is there

  • Tricky SQL SELECT statement - combine two rows into two columns 2010-07-09

    My problem: I have a table with a Channel <int> and a Value <float> column, along with a timestamp and a couple of other columns with additional data. Channel is either 1 or 2, and there is either 1 or 2 rows that have everything except c

  • Combine multiple rows into one MySQL Join 2012-01-12

    I couldn't think of a better way to ask this question, but here goes: I have 2 tables. The first is a table of items for label printing jobs. We'll call it pj_items The columns are simply: job_id, part_num and qty The second table is a list of finish

  • how to insert multiple rows into the database at the same time? 2012-03-05

    I m doing a mysql database project. In my Webpage there r multiple rows of textboxes,where each row of textboxes correspond to a single entry in the database. When the user enters data into a single row and press the save button the values r automati

  • SQL how to create multiple row from a single row 2012-04-24

    Hi I am new to SQL server 2008 I want to expand a single row to multiple rows based on another colomn, e.g date value 7-2011 5 Results: 2011-07-01 2011-08-01 2011-09-01 2011-10-01 2012-11-01 the date shoild be first day of current and next month repe

  • how to combine multiple lines into one? 2010-11-04

    I am trying to figure out a way of combining the below Dim statements into one line and maybe also combining Return dblResult into there. currently it is three lines and I have been told that it is possible but kind of lost of how to do it - can anyo

  • TSQL Combining Multiple Rows Into One Row 2011-09-16

    I am trying to create a query to combine the following information: FileID ErrorCode ErrorDesc ErrorCount 1 4 Bad File Name 3 2 6 Bad File Code 56 3 4 Bad File Name 2 3 12 Line Length Invalid 3 3 17 Missing Req Fields 150 I want to combine all rows b

  • how to combine multiple *argv into a char* type messge 2011-10-13

    I wrote a TCP socket client program which allows user to input the IP, port, and message as arguments. It is like: ./a.out 127.0.0.1 555 test message My question is, how to combine "test" (argv[3]) and "message" (argv[4]) and more into a char *messag

  • How to insert multiple rows into a SQLite 3 table? 2012-04-06

    In MySQL I'd use INSERT INTO `mytable` (`col1`, `col2`) VALUES (1, 'aaa'), (2, 'bbb'); but this causes an error in SQLite. What is the correct syntax for SQLite? --------------Solutions------------- This has already been answered before here: Is it p

  • How to combine multiple sheets into one sheet of Excel Database (db) 2013-09-21

    I have a question about combining 15 separate sheets over 1000 rows into one big database in excel only. VB is ok but it must be in Excel. I do not want to keep copying & pasting. Is there a simpler way to combine with a click of a VB button? P.S

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 3.590 (s). 14 q(s)