Created
April 16, 2020 06:14
-
-
Save MattiasSp/2081bb4b289191a916701b4a864e0fcb to your computer and use it in GitHub Desktop.
An SQL script that uses recursive CTE to do ordered cumulative string concatenation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ============================================================================ | |
-- Filename: cumulative_string_concatenation.sql | |
-- | |
-- Author: Mattias Spångmyr | |
-- Create date: 2020-04-16 | |
-- Description: Uses a recursive common table expression (recursive CTE) to | |
-- cumulatively concatenace string values that have a defined | |
-- order and share a common identifier. | |
-- | |
-- SQL Server | |
-- version: MS SQL Server 2017 | |
-- | |
-- Copyright: GPL v3 or later. | |
-- This program is free software: you can redistribute it and/or | |
-- modify it under the terms of the GNU General Public License as | |
-- published by the Free Software Foundation, either version 3 of | |
-- the License, or (at your option) any later version. | |
-- | |
-- This program is distributed in the hope that it will be | |
-- useful, but WITHOUT ANY WARRANTY; without even the implied | |
-- warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR | |
-- PURPOSE. See the GNU General Public License for more details. | |
-- | |
-- You should have received a copy of the GNU General Public | |
-- License along with this program. If not, see | |
-- https://www.gnu.org/licenses/. | |
-- | |
-- Docs | |
-- reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql | |
-- | |
-- ============================================================================ | |
DECLARE @TEMP AS TABLE ( | |
id int null, | |
val varchar(100) null, | |
i int null | |
) | |
INSERT INTO @TEMP | |
VALUES (1, '2', 1), (1, '3', 2), (1, '4', 3), (1, '5', 4), | |
(2, '1', 1), (2, '3', 2), (2, '4', 3), (2, '5', 4), | |
(3, '2', 1), (3, '4', 2), (3, '1', 3), (3, '5', 4), | |
(4, '3', 1), (4, '5', 2), (4, '2', 3), (4, '1', 4), | |
(5, '4', 1), (5, '3', 2), (5, '2', 3), (5, '1', 4); | |
WITH gr (n, id, val, i) AS | |
( | |
SELECT 1 | |
, id | |
, val | |
, i | |
FROM @TEMP | |
WHERE i = 1 | |
UNION ALL | |
SELECT gr.n + 1 | |
, temp.id | |
, CAST(CONCAT_WS(',', gr.val, temp.val) AS varchar(100)) | |
, temp.i | |
FROM @TEMP temp | |
INNER JOIN gr ON temp.id = gr.id AND temp.i = (n + 1) | |
) | |
SELECT id, val, i | |
FROM gr | |
ORDER BY id, i | |
OPTION (MAXRECURSION 1000) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment