sql aggregate and split

Question

I have this table Table_1 :

pk Column_1
1  addcd
2  swrrh
3  dggdd
4  wdffa

I want to merge and split the result into 4 letters that will return something like this

addc
dswr
rhdg
gddw
dffa

Note: left over letters will be discarded

I tried to use string_agg, but I don't have any idea what to do next.

@EzLo, sorry i forgot to add primary key, which serve as ordering column — Ed Bangga, Aug 20 '19 at 07:56

Thom A · Accepted Answer · 2019-08-20T13:53:52.297

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

Thanks @Larnu, I tried this and its working, but i changed WHERE SUBSTRING(A.YourString,(I*4)+1,4) <> '' to WHERE len(SUBSTRING(A.YourString,(I*4)+1,4)) = 4; to exclude the left over characters. But now I'm doubtful if I'll use string_agg, as what Gordon Linoff mentioned above that string_agg can't guarantee correct ordering. — Ed Bangga, Aug 21 '19 at 00:06
@metal his statement is wrong, you can if you have a column to order on... That's is why my `STRING_AGG` expression *has* an `ORDER BY` clause. — Thom A, Aug 21 '19 at 06:46

Gordon Linoff · Answer 2 · 2019-08-22T00:36:31.250

0

This solution is appropriate for older versions of SQL Server. Larnu's answer is correct in more recent versions.

Let's use recursive CTEs. The idea is to create one large string and then chop it up into pieces. What fun!

with tt as (
      select t.*, row_number() over (order by pk) as seqnum
      from t
     ),
     bigstring as (
      select convert(varchar(max), column_1) as bigstring, seqnum
      from tt
      union all
      select concat(bigstring.bigstring, tt.column_1), tt.seqnum
      from bigstring join
           tt
           on tt.seqnum = bigstring.seqnum + 1
     ),
     string4 as (
      select top (1) left(bigstring, 4) as string4, stuff(bigstring, 1, 4, '') as rest4, 1 as lev
      from bigstring
      order by len(bigstring.bigstring) desc
      union all
      select left(rest4, 4), stuff(rest4, 1, 4, ''), lev + 1
      from string4
      where rest4 > ''
     )
select string4.string4
from string4
order by lev;

Two recursive CTEs in one query!

Here is a db<>fiddle.

edited Aug 22 '19 at 00:36

answered Aug 21 '19 at 01:43

Gordon Linoff

1,242,037
58
646
786

Thanks! this is working but I added where len(string4) = 4 to exclude the left overs. – Ed Bangga Aug 21 '19 at 02:02
The problem here with an rCTE is it's as the name suggests, recursive. For large datasets this could well be slow. – Thom A Aug 21 '19 at 06:49
@Larnu, I agree with you, i tried to display the detail execution plan and it resulted to "The statement terminated. The maximum recursion 100 has been exhausted before statement completion". For now I'll leave this question open. – Ed Bangga Aug 21 '19 at 06:56
The `STRING_AGG` solution could work well here @metal . It won't recurse, and like I said Gordon's statement about ordering is wrong; as these is a clear *documented* section on the sorting when using the function. If you're unsure, then test it first. – Thom A Aug 21 '19 at 07:09
1

@Larnu . . . I removed the comments and upvoted your answer. I left this only because it works on recent but pre-string_agg() versions of SQL Server. – Gordon Linoff Aug 22 '19 at 00:37

sql aggregate and split

2 Answers2