Let's say I have a table like the following:
LineId Linea
------- -----------
1 Linea 1
2 Linea 2
3 Linea 3
4 Linea 4
And I'm looking to get an output like this:
LineId Linea
------- -----------
1 Linea 1,
2 Linea 1, Linea 2,
3 Linea 1, Linea 2, Linea 3,
4 Linea 1, Linea 2, Linea 3, Linea 4,
I think the idea is understood, cumulatively concatenate each line, and in the order given by LineId
(fundamental). A very rustic way to solve it would be to do something like this:
DECLARE @Temporal VARCHAR(8000)
UPDATE #Ejemplo
SET @Temporal = ISNULL(@Temporal,'') + Linea + ', ',
Linea = @Temporal
FROM #Ejemplo
But, this is where I wonder: since the tables don't have a natural order and you can't set a ORDER BY
in a statement UPDATE
either or at least I don't know how to do it, the previous statement, which works fine in the example, doesn't guarantee me the update order. Might as well be outputting something like this
LineId Linea
------- -----------
1 Linea 1,
2 Linea 1, Linea 3, Linea 2,
3 Linea 1, Linea 3,
4 Linea 1, Linea 3, Linea 2, Linea 4,
Additional details:
- The example is a test of the idea of the problem, it will work fine, possibly always
- In reality, I have a similar case, a legacy code, which processes sequential text files
- Erratically, cases are detected where the insertion order would not be maintained
- I am clear that there is no "insertion order", I am not complaining about the behavior of SQL Server, it is expected. What yes, this behavior began to be verified when changing a version of the engine (2008 to the next)
- The solution that I don't like, but it works, is to use cursors and update by row
- I'd like to see if there is a more elegant or natural way to solve it
- So far I have tried without much success: a) Add an identity to the table that represents the order, to see if the engine uses it by default b) Go through the generation of an XML, but so far I have not achieved the expected result.
To play the data
CREATE TABLE #Ejemplo (
LineId INT IDENTITY,
Linea VARCHAR(8000)
)
INSERT INTO #Ejemplo(Linea)
VALUES ('Linea 1'), ('Linea 2'), ('Linea 3'), ('Linea 4')
There are several options to do this.
My first suggestion comes from one of the SQL Server professionals I respect the most. It has exceptional performance and has had an exhaustive series of tests to try to break the code but the refined version has not been found to be flawed. In English they are called "Quirky Update" and it requires following a series of rules to do it correctly. Everything is explained in this article but I leave a summary of what to do.
The modified code ends like this:
The other option I can think of is to upgrade via a CTE. The problem is that you end up with a "triangular JOIN" which generates too many rows and can be a tremendous problem and slower than using a cursor to update row by row. By the way, a forward-only static cursor inside a single transaction can be quite efficient too.
This is the second idea