CODEWITHSUNDEEP
sqlsql-serverconcatenationsql-server-2017
ItalianStallion4215
ItalianStallion4215

Reputation: 138

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.

Upvotes: 0

Views: 168

Answers (2)

Ravi Desai
Ravi Desai

Reputation: 150

As mention by Gordon you can use string_agg (Doc) function if you are using SQL Server version 2017 or above.

In case if you need same functionality for SQL Server version below 2017, use this:

SELECT STUFF((
            SELECT CONCAT (
                    ','
                    ,LandingZoneCol
                    )
            FROM @LandingZoneMapping LZM
            WHERE StagingCol = 'ID'
            ORDER BY ConcatenateOrder
            FOR XML PATH('')
            ), 1, 1, '') AS Result

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

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.

Upvotes: 1

Related Questions