I want to get distinct value from STRING_AGG but unfortunately i didn't figure out this is my query
SELECT
proinfo.ProjectN
,proinfo.ProjectName
,cust.Name as 'Customer'
,sum(daiPaiPro.Quantity*ProShip.Weight)as 'Weight'
,STRING_AGG( ISNULL(ProShip.Designation , ' '), ' ,') WITHIN GROUP (ORDER BY ProShip.Designation ASC) As 'Parts'
,[DateMAD] as 'DateMAD'
,[Note]
,[CreationDate]
FROM [dbo].[PlanningCHByPart] daiPaiPro
inner join ProjectShipping ProShip on ProShip.id=[FK_idPartShip]
inner join ProjectInfo proinfo on proinfo.id=ProShip.IdProject
inner join Employee emp on ID_Employee=daiPaiPro.[UserID]
inner join Customer cust on cust.ID=proinfo.FK_Customer
Group By
proinfo.ProjectN
,proinfo.ProjectName
,cust.Name
,[DateMAD]
,[Note]
,[CreationDate]
Order By [DateMAD]
What I have tried:
I try this
,(select STRING_AGG( ISNULL(x.Designation , ' '), ' ,') WITHIN GROUP (ORDER BY x.Designation ASC) from
(select ProjectShipping.Designation from
ProjectShipping group by ProjectShipping.Designation) x) As 'Parts'
but then I get all Designation value from ProjectShipping table