Search code examples
sqlsql-serverconcatenationsql-server-2017

SQL Server 2017 - Concatenate values based on order number

I am doing concatenation of values where there is grouping value greater than 1. This works fine, but now I am trying to figure out how to utilize an sequence/order number in which to concatenate the values in that order. Is there a way I can do this?

So for example, I have a table which has the following:

Table

I need the ability to concatenate Field1 and Field4 since the StagingCol is the same name and I also need to be able to concatenate in the order provided in the ConcatenateOrder column. I can't have it out of sequence i.e. Field4 + Field1

This is a snippet of the code I have so far which is working fine, it concatenates the two LandingZoneCol values...

--DECLARATION OF LANDING ZONE FIELD NAMES CONCATENATED TOGETHER AND DELMITED BY A COMMA WHERE VALUE NEEDS TO BE CONCATENATED (I.E. SUBSCRIBER + SEQ# = MEMBER_ID)
SELECT @ConcatLandingZoneFieldNames = ISNULL(@ConcatLandingZoneFieldNames,'') + ISNULL(LandZoneMapping.LandingZoneFieldName,'') + ', '     
FROM @LandingZoneMapping AS LandZoneMapping
WHERE LandZoneMapping.StagingColumnName <> 'BreadCrumb'
AND LandZoneMapping.StagingColumnName IN (SELECT StagingColumnName
    FROM @TEST 
    WHERE Total > 1)

--DECLARATION OF VARIABLES
SET @ConcatLandingZoneFieldNames = CONCAT('CONCAT(',SUBSTRING(@ConcatLandingZoneFieldNames,1,LEN(@ConcatLandingZoneFieldNames)-1),')')

Current Results CONCAT(Field1, Field4)

Expected Results CONCAT(Field1, Field4)

Although both Current and Expected are the same right now, I want to ensure that the concatenation of the values are concatenated in the correct order. If I was to flip the ConcatenateOrder numbers in the above table then the outcome would be different. The "Current" Results would end up being CONCAT(Field1, Field4) but the "Expected Results" should be CONCAT(Field4, Field1)

Any help would be greatly appreciated.

Solution

  • Your code looks like SQL Server. You an use string_agg():

    select string_agg(lzm.landingzonecol, ',') within group (order by lzm.concatenateorder)
from @LandingZoneMapping lzm
where lzm.stagingcol = 'ID';

    You can control the ordering with the order by clause.