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?

I would suggest you to create a scalar function which will produce the concatenated value based on the sorder as input parameter. Otherwise I can't find a way to make this logic with a set of data. — Shukri Gashi, Jul 07 '16 at 13:50
Do you need to return a TEXT column for only one row (SORDER=X), or do you need to generate it for all rows ? — Thomas G, Jul 07 '16 at 14:13

score 0 · Accepted Answer · answered Jul 07 '16 at 14:00

0

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

answered Jul 07 '16 at 14:00

Shukri Gashi

535
2
10

WX2 doesnt have such scalar functions and we need to do this in plain SQL. Also the syntax looks like SQL-SERVER!! – Srini V Jul 07 '16 at 14:07
Yes, it's T-SQL. I missed that you are doing this in wx2. Sorry. – Shukri Gashi Jul 07 '16 at 14:13

How to manipulate concatenated columns in SQL

1 Answers1