Thursday, February 1, 2024

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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog