![]() ![]() This solution is perhaps the easiest to understand and the most flexible, where performance isn’t an issue. However if the number of rows is small, as it is here, then this can be a useful approach. Generally looping constructs in SQL are to be avoided as they can perform badly. SET =( SELECT Txt FROM ConcatenationDemo WHERE SET = ( SELECT MAX (RowID ) FROM ConcatenationDemo ) The following code uses a WHILE loop (this could be rewritten to use a cursor) : We can do the same thing in SQL Server using either a while loop or cursor. In the procedural world the chosen solution would probably be some sort of looping construct. Solution 3 : Using a WHILE loop or Cursor This is a significant limitation of this approach. One thing that you may have noticed is that the code is specific for the number of rows in the table – the code would have to be modified if there was an extra row. Note that in the this example no aggregation is necessary so this could equally well be a MIN(). Normally we would have some type of aggregation as the rows are pivoted to columns, hence the use of the MAX() here. I’ve used the PIVOT command to convert the rows to an equivalent number of columns and then concatenated the result together. PIVOT ( MAX (Txt ) FOR RowID IN (, ,, ,, ,, ) However, since this example is rather contrived, this shouldn't be an issue for "genuine" strings where the normal ASCII sort order should be acceptable.Ĭleaned up (extraneous fields removed) version - same result.SELECT + ',' + + ',' + + ',' + + ',' + + ',' + + ',' + + ',' + AS TxtįROM ( SELECT 'Total' AS AC, ,, ,, ,, , 9, 10, 11 - it appears as 10, 11, 9 - which is the individual csv strings sorted ASCII wise, not numerically. Unfortunately, there is no way of sorting the strings internally by record - i.e. To have the first group of 2 contain 9, 10 & 11, the rn is necessary. In order to get the first 3 strings into the first slot per id, we have to keep the rn field down through the SQL - for example, group 2 has 9, 10, 11 & 12. ROW_NUMBER() OVER (PARTITION BY id, cal_1 ORDER BY id, rn1) AS cal_2 So, we now have our csv strings in groups of 3, by virtue of their id field and the cal_2 field which allows us to do use SQLite's GROUP_CONCAT() built-in aggregate function as follows (I found this post helpful): SELECT ROW_NUMBER() OVER (PARTITION BY id, cal_1 ORDER BY id, csv) AS cal_2 Now, we have to obtain a handle on our groups of threes - putting each group of three into a separate group. The field that interests us is cal_1, which starts at 0 and cycles through 1 & 2 and then goes back to 0 - from the beginning! ![]() So, we can that rn1 % 3 isn't very helpful - it starts 1, 2 and then drops back to 0. The next step is to start the grouping of the csvs by threes. Id, csv, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn1ġ 1 1 <<- csv is a string (TEXT), rn is an INTEGER! Note also that the individual csvs are strings (TEXT) and do not represent numbers (this saved on typing.). The key here is to get the csvs into groups of 3 - therefore the modulus operator ( %) is the obvious choice, but even with that, there's still a bit of work to be done. Populated it (with extra data for testing): INSERT INTO csvs (id, csv) VALUES In order to answer this question, I did the following (all of the code below is available on the fiddle here): CREATE TABLE IF NOT EXISTS csvs ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |