How to manipulate concatenated columns in SQL

Question

Say I have the below dataset

WITH dataset (hno,sorder,level,dummy,maxorder) AS 
(
SELECT 1,   1,  'val1', 'NULL1',  5 FROM dual UNION ALL
SELECT 1,   2,  'val2', 'NULL2',  5 FROM dual UNION ALL
SELECT 1,   3,  'val3', 'NULL3',  5 FROM dual UNION ALL
SELECT 1,   4,  'val4', 'NULL4',  5 FROM dual UNION ALL
SELECT 1,   5,  'val5', 'NULL5',  5 FROM dual)
SELECT *
FROM dataset;

HNO SORDER  LEVEL   DUMMY   MAXORDER
1       4   val4    NULL4   5
1       2   val2    NULL2   5
1       3   val3    NULL3   5
1       1   val1    NULL1   5
1       5   val5    NULL5   5

And I need to have another column called TEXT

HNO SORDER  LEVEL   DUMMY   MAXORDER    TEXT
1       4   val4    NULL4   5           val1,val2,val3,val4,NULL5
1       2   val2    NULL2   5           val1,val2,NULL3,NULL4,NULL5
1       3   val3    NULL3   5           val1,val2,val3,NULL4,NULL5
1       1   val1    NULL1   5           val1,NULL2,NULL3,NULL4,NULL5
1       5   val5    NULL5   5           val1,val2,val3,val4,val5

The idea is to manipulate dynamic placeholders for the SQL. So if the sort order is a range between 1...n then for a SORDER value x, I need to return values from column LEVEL for 1 to x and then from dummy for x+1 to n and all of them concatenated as comma separated values and most importantly all in one SQL. This max order could be any number and it is not fixed. WX2 lacks recursive CTE which makes it difficult.

Any pointers?

Answer 1

This function concatenates strings based on the logic you described (test is the name of table with data):

CREATE FUNCTION Concatenate_string
(
    @sorder int
)
RETURNS varchar(1000)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result varchar(1000)=''
    declare @i int=1
    while @i<=(select max(sorder) from test)
    begin
        set @result=@result + (select case when @i <= @sorder then level else dummy end+',' from test where sorder=@i)
        set @i=@i+1
    end

    return @result


END
GO