Post reply

Need help on Concatenate based on Group and Aggregate in SQL 2016

  • April 18, 2020 at 2:15 pm

    #3743925

    ************* Got the solution *******************

    Hi all,

    I hope and pray for everyone to be safe during this Covid-19 tough time.

    I need your help on "Concatenate based on Group and Aggregate in SQL 2016".  I have found a possible solution in SQL 2017 using STRING_AGG( )  but my current sql sever version is SQL 2016.

    below the table script and my work. Expected output is attached. Thanks in advance.

    CREATE TABLE [dbo].[Test]([BU]  varchar(10) NULL,[Packtype] varchar(10) NULL,[moldno] int  NULL,[MoldName]  varchar(30) NULL,[Weight]  int NULL ) ON [PRIMARY]

    GO

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('L', 'Pack1', 111, 'Mold-A', 100)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('L', 'Pack1', 222, 'Mold-B', 200)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('L', 'Pack1', 333, 'Mold-C', 300)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('L', 'Pack2', 444, 'Mold-D', 200)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('L', 'Pack2', 111, 'Mold-A', 200)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('L', 'Pack2', 666, 'Mold-F', 200)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('L', 'Pack2', 555, 'Mold-E', 200)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('B', 'Pack3', 111, 'Mold-A', 100)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('B', 'Pack3', 444, 'Mold-D', 100)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('B', 'Pack3', 333, 'Mold-C', 100)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('B', 'Pack3', 555, 'Mold-E', 100)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('B', 'Pack4', 222, 'Mold-B', 200)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('B', 'Pack4', 333, 'Mold-C', 100)

    INSERT [dbo].[Test] ([BU], [Packtype], [moldno], [MoldName], [Weight]) VALUES ('B', 'Pack4', 444, 'Mold-D', 200)

    GO

    This is what i have tried, but i am not successful.

    SELECT BU,PackType

    ,STUFF(

    (

    SELECT ';' + MoldName

    FROM Test as ct1

    WHERE ct1.Packtype = ct2.Packtype

    FOR XML PATH ('')

    ), 1, 1, ''

    ) AS MoldDesc

    FROM Test as ct2

    GROUP BY BU,PackType

     

    Thanks,Santosh.

     

    • This topic was modified 4 years, 5 months ago by  ssskumar4u.
    • This topic was modified 4 years, 5 months ago by  ssskumar4u.
    • This topic was modified 4 years, 5 months ago by  ssskumar4u.
    • This topic was modified 4 years, 5 months ago by  ssskumar4u.

    Attachments:
    You must be logged in to view attached files.

  • April 19, 2020 at 1:06 pm

    #3743996

    Hi all,

    I got the solution myself.. closing this topic Thanks a lot.

  • April 19, 2020 at 2:07 pm

    #3743999

    What was it? Use STUFF() or STRING_AGG() with the PARTITION clause?

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

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