How to use the STRING_AGG function to Aggregate the Strings in SQL Server

How to Aggregate or Concatenate the Numbers and Strings using STRING_AGG Function in SQL Server

Scenario:
Suppose we a sample Categories table as follows.. 

/* Create sample Categories table */

CREATE TABLE [dbo].[tbl_Categories]

    (

        [CatID] [int] NULL,

        [MainCat] [varchar](5) NULL,

        [SubCat] [varchar](5) NULL

    ) ON [PRIMARY]

    GO

/* Insert sample data into the Categories table */

INSERT INTO [dbo].[tbl_Categories] ([CatID], [MainCat], [SubCat])

VALUES (121, 'A','ABC'), (122,'B','BCD'), (123,'B',NULL),(124,'B','DEF'),

(125,'C','EFG'), (126,'C','FGH'),(127,'D','GHI'),(128,NULL,NULL),(129,'D','IJK'),

(NULL,'E','JKL')

Select * From [dbo].[tbl_Categories] ;

Now we want to Aggregate the [CatID], [SubCat] Fields for each Group of the [MainCat].

We can achieve this Scenario using STRING_AGG(expression, separator) Function as shown below:

/* Aggregate (Concatenate) the String and Numbers */

SELECT

    MainCat,

    STRING_AGG(CAST(CatID AS VARCHAR(5)) + '-' + SubCat ,'|')

        WITHIN GROUP (ORDER BY MainCat ) AS Cat_Hierarchy

FROM

    [dbo].[tbl_Categories]

GROUP BY MainCat

Result:

Notes:
-- If the Whole expression is NULL then the STRING_AGG function returns the NULL.

-- If any one of the values in the expression is NULL then the STRING_AGG function will skip that value and separator as well.

-- The WithIn Group with Order By clause will helps to Sort the items while aggregating.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------