Multiple pivot with remove duplicate in string_agg function

Question

Hi,

I have a table like below

create table #Temp (ID nvarchar(50), Amount Decimal(18,2), AmountType varchar(10), Code nvarchar(100));

insert into #Temp values (1001, 100, 'CR', 'AAA')
insert into #Temp values(1001, 100, 'CR', 'BBB')
insert into #Temp values(1001, 200, 'CR', 'AAA')
insert into #Temp values(1001, 100, 'DR', 'AAA')
insert into #Temp values(1001, 100, 'DR', 'BBB')
insert into #Temp values(1001, 300, 'DR', 'AAA')
insert into #Temp values(1001, 400, 'DR', 'CCC')

select * from #Temp;

I used the below query to get below result

select ID,AmountType, STRING_AGG(Code,',') as Type, SUM(Amount) as Amount from #Temp group by ID,AmountType

but i need the result like below
create table #Result (ID nvarchar(50), CR varchar(10), DR varchar(10), CRCode nvarchar(100), DRCode nvarchar(100));
insert into #Result values (1001, 400, 900, 'AAA,BBB', 'AAA,BBB,CCC')
select * from #Result
drop table #Result

drop table #Temp

Note :

1. while using string_agg, i need to remove duplicate values
2. need to use pivot like above result

Thanks In Advance

Answer 1

To do the pivot, just add another cte to the code @Russel Loski gave you, so

 with cteAmount as (  
 SELECT ID, AmountType, Code, SUM(AMOUNT) as Amount  
 FROM #Temp  
 GROUP BY  ID, AmountType, Code),  
  
 cteCode As  
 (SELECT  
 ID, AmountType  
 , STRING_AGG(Code,',') Within Group (Order By Code) as Type  
 , Sum(Amount) as Amount  
 From cteAmount  
 GROUP BY ID, AmountType)  
  
 Select ID,   
   IsNull(Max(Case When AmountType = 'CR' Then Amount End), 0) As CR,   
   IsNull(Max(Case When AmountType = 'DR' Then Amount End), 0) As DR,   
   IsNull(Max(Case When AmountType = 'CR' Then Type End), '') As CRCode,   
   IsNull(Max(Case When AmountType = 'DR' Then Type End), '') As DRCode  
 From cteCode  
 Group By ID  
 Order By ID;  

Tom

Answer 2

I would aggregate first on the ID, AmountType and Code first, then use string agg on that:

with cte as (
SELECT ID, AmountType, Code, SUM(AMOUNT) as Amount
FROM #Temp
GROUP BY  ID, AmountType, Code)
SELECT
ID, AmountType
, STRING_AGG(Code,',') as Type
, Sum(Amount) as Amount
From cte
GROUP BY ID, AmountType;

Answer 3

Hi @kasim mohamed ,

Please also check:

    ;WITH cte  
    as(SELECT DISTINCT ID,AmountType,Code,  
    SUM(Amount) OVER(PARTITION BY ID,AmountType)as Amount   
    from #Temp)   
    ,cte2 as(SELECT * FROM (  
    SELECT ID,AmountType,Amount   
    FROM cte  
    GROUP BY ID,AmountType,Amount) t  
    PIVOT (MAX(Amount) FOR AmountType IN([CR],[DR])) P)  
    ,cte3 as(  
    SELECT * FROM (  
    SELECT ID,AmountType,STRING_AGG(Code,',') as Type  
    FROM cte  
    GROUP BY ID,AmountType,Amount) t  
    PIVOT (MAX(Type) FOR AmountType IN([CR],[DR])) P)  
      
    SELECT c2.*,c3.[CR] CRCode,c3.[DR] DRCode   
    FROM cte2 c2  
    JOIN cte3 c3 ON c2.ID=c3.ID  

Output:

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.