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
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.