Scenario:
Suppose we a sample Categories table as follows..
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] ;
/* 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
Notes:
-- If the Whole expression is NULL then the STRING_AGG function returns the NULL.
-- 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.