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 as below
CompanyId PackageId partsfamilyid countPartsValues MSLIDValue
1003808 4894 1871020 4 (2)1|(2)N/A
1009541 4820 1871000 5 (3)N0|(2)N/A
1009320 4800 1870000 3 (2)N/A|(1)N0
1009300 4700 1860000 1 (1)A
What I have tried:
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