Click here to Skip to main content
15,892,298 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to get distinct value from STRING_AGG but unfortunately i didn't figure out this is my query
SQL
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
SQL
,(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
Posted
Updated 3-Jan-20 15:57pm
v2

1 solution

I would use a CTE to create a table of distinct strings, then use that in your STRING_AGG function.
 
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