Post reply

concate string and order by in display

  • February 25, 2020 at 4:19 pm

    #3728647

    Hello,

    I need one logical help to develop one logic,

    below is dll

    CREATE TABLE #X1
    	(
    		ID VARCHAR(10),
    		CODE VARCHAR(10),
    		PrimaryInd INT
    	)


    INSERT INTO #X1 VALUES ('12345','01',0)
    INSERT INTO #X1 VALUES ('12345','03',0)
    INSERT INTO #X1 VALUES ('12345','04',1)

    I need to concate the string and order by PrimaryInd.

    desired output

    ID	Code
    12345	04,01,03

    Can anyone please help me develop this logic.

  • February 25, 2020 at 7:02 pm

    #3728698

    One option would be to concatenate the values of the rows using for xml path and stuff - along the lines of:

    SELECT DISTINCT ID, 
    	STUFF((
            SELECT ', ' + CODE
            FROM #X1
    		ORDER BY PrimaryInd DESC
            FOR XML PATH('')
            ), 1, 1, '')
         AS Code
    FROM #X1

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply