[Solved] sql aggregate and split [closed]

by


This uses STRING_AGG to aggregate the strings into one long one, and then a Tally Table to split into the new rows. It is assumed you have a column to order by. if you do not, you cannot achieve what you are after without one as data in a table is stored in an unordered heap.

CREATE TABLE dbo.YourTable (YourID int IDENTITY,
                            Yourcolumn varchar(5));

INSERT INTO dbo.YourTable (Yourcolumn)
VALUES('addcd'),
      ('swrrh'),
      ('dggdd'),
      ('wdffa');
GO

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS( 
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS I
    FROM N N1, N N2, N N3, N N4), --This is likely over kill here, but I've assumed you'll have a much larger data set
Agg AS(
    SELECT STRING_AGG(YT.YourColumn,'') WITHIN GROUP (ORDER BY YT.YourID)   AS YourString
    FROM dbo.YourTable YT)
SELECT SUBSTRING(A.YourString,(I*4)+1,4)
FROM Tally T
     CROSS JOIN Agg A
WHERE SUBSTRING(A.YourString,(I*4)+1,4) <> '';
GO

DROP TABLE dbo.YourTable;

db<>fiddle

2

solved sql aggregate and split [closed]