You may use STUFF Function to achieve this, below script should give you proper result
declare @tbl table(Acc int, [Plan1] nchar)
insert into @tbl
select 121,'A'
UNION ALL
SELECT 131,'B'
UNION ALL
SELECT 141,'C'
UNION ALL
SELECT 121,'B'
UNION ALL
SELECT 145,'D'
UNION ALL
SELECT 131,'A'
UNION ALL
SELECT 141,'A'
UNION ALL
SELECT 190,'R'
UNION ALL
SELECT 200,'S'
UNION ALL
SELECT 121,'A'
select Acc,
STUFF((Select ',' + cast(T1.Plan1 + cast(Count(1) as nvarchar) as nvarchar)
FROM @tbl T1
WHERE T1.Acc = T.Acc
GROUP BY T1.Plan1 for xml path('')),1,1,'')
FROM @tbl T
group by Acc
Regards,
Sujay C.
Submit an article or tip