Click here to Skip to main content
15,435,848 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted
Updated 21-Jul-22 20:00pm
v2

1 solution

You need to concat count of parts with description. See:

SQL
CREATE TABLE DUMMY
(
  CID INT,
  SID VARCHAR(1),
  CNT INT
);

INSERT INTO DUMMY(CID, SID, CNT)
VALUES(1, 'A', 2),
(1, 'B', 1),
(1, 'C', 3);

SELECT CID, STRING_AGG(CONCAT(SID, '(', CAST(CNT AS VARCHAR(10)), ')'), '|') AS Txt
FROM DUMMY
GROUP BY CID;


Result:
CID 	Txt
1 		A(2)|B(1)|C(3)


SQL Server 2019 | db<>fiddle[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900