Post reply

How to get count and values merged by string aggregate

  • July 21, 2022 at 5:22 pm

    #4066565

    I work on sql server 2019 i can't get count and values separated stick by using string aggregate function

    order not important when arrange count and values sticks separated .

    my issue is can't merge count per value with msl value it

    formate as (count)value | (count)value etc...

    code sample

     create table #final
     (
     CompanyId int,
     PackageId int,
     partsfamilyid int,
     countparts int
     )
     insert into #final(CompanyId,PackageId,partsfamilyid,countparts)
     VALUES
     (1003808,4894,1871020,4),
     (1009541,4820,1871000,5),
     (1009320,4800,1870000,3),
     (1009300,4700,1860000,1)
        
     create table #finaldetails
     (
     CompanyId int,
     PackageId int,
     partsfamilyid int,
     countPartsValues int,
     MSLIDValue varchar(50)
     )
     insert into #finaldetails(CompanyId,PackageId,partsfamilyid,MSLIDValue,countPartsValues)
     values
     (1003808,4894,1871020,'1',2),
     (1003808,4894,1871020,'N/A',2),
        
     (1009541,4820,1871000,'N0',3),
     (1009541,4820,1871000,'N/A',2),
        
     (1009320,4800,1870000,'N0',1),
     (1009320,4800,1870000,'N/A',2),
        
     (1009300,4700,1860000,'A',1)

    expected result

    so how to merge count per value with mslidvalue ?

    what i try is

    select m.CompanyId,m.PackageId,m.partsfamilyid,max(m.countparts) as countparts,STRING_AGG(CONVERT(VARCHAR(MAX), MSLIDValue),'|') WITHIN GROUP(ORDER BY MSLIDValue ASC) AS MSLDIFF  from #final m
     inner join #finaldetails v  on v.companyid=m.companyid and v.partsfamilyid=m.partsfamilyid  and v.packageId=m.packageId
     group by m.CompanyId,m.PackageId,m.partsfamilyid

     

  • July 21, 2022 at 5:38 pm

    #4066568 

    select m.CompanyId,m.PackageId,m.partsfamilyid,max(m.countparts) as countparts,
        STRING_AGG('(' + CONVERT(VARCHAR(8000), countPartsValues) + ')' + CONVERT(VARCHAR(8000), MSLIDValue),'|') WITHIN GROUP(ORDER BY MSLIDValue ASC) AS MSLDIFF  
    from #final m
    inner join #finaldetails v  on v.companyid=m.companyid and v.partsfamilyid=m.partsfamilyid  and v.packageId=m.packageId
    group by m.CompanyId,m.PackageId,m.partsfamilyid
    order by m.CompanyId,m.PackageId,m.partsfamilyid

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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