none
Produce DISTINCT values in STRING_AGG RRS feed

  • Question

  • I want to get distinct value from STRING_AGG but unfortunately i didn't figure out how 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
      Where MONTH([DateMAD])=@month and YEAR([DateMAD])=@year
      Group By
           proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name 
      ,[DateMAD] 
      ,[Note]
          ,[CreationDate] 
      Order By [DateMAD]

    • Edited by qwertyess Friday, January 3, 2020 7:13 PM
    Friday, January 3, 2020 7:12 PM

Answers

  • Rachel's cte should should use GROUP BY, not DISTINCT and it should SUM the Weights.  And the GROUP BY clause in the cte should include ProShip.Designation.  That's because when you compute the weights without summing them, you can have one Designation value with multiple Weight values, do when you do DISTINCT you get multiple occurrences of the same Designation and that leads to duplicates in the STRING_AGG.  So I think you want

    ; with cte as (
    SELECT 
      proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name 
      ,SUM(daiPaiPro.Quantity*ProShip.Weight) 'Weight'
      ,ProShip.Designation 
      ,[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
      Where MONTH([DateMAD])=@month and YEAR([DateMAD])=@year
    Group By
       proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name 
      ,[DateMAD] 
      ,[Note]
      ,[CreationDate]
      ,ProShip.Designation
    )
    SELECT 
       cte.ProjectN 
      ,cte.ProjectName
      ,cte.Name as 'Customer'
      ,sum(Weight)as 'Weight'
      ,STRING_AGG( ISNULL(cte.Designation , ' '), ' ,') WITHIN GROUP (ORDER BY cte.Designation ASC)  As 'Parts'
      ,[DateMAD] as 'DateMAD'
      ,[Note] 
      ,[CreationDate] 
    FROM cte
    Group By
       cte.ProjectN 
      ,cte.ProjectName
      ,cte.Name 
      ,[DateMAD] 
      ,[Note]
      ,[CreationDate] 
    Order By [DateMAD]
    Tom
    • Marked as answer by qwertyess Friday, January 10, 2020 10:43 AM
    Friday, January 10, 2020 9:11 AM

All replies

  • What about using a subquery to get a distinct list first:

    create table test (ProjectN  int, Designation  varchar(20))
    
    insert into test values (1,'bbb'),(1,'Ybbb'),(1,null),(1,'Ybbb')
    ,(1,'abbb'),(1,'Y4Ybbb'),(1,null),(1,'Ybbb')
    ,(2,'2bbb'),(2,'1Ybbb'),(2,null),(2,'abbb')
    
    
    
    select ProjectN  
    ,STRING_AGG(Designation, ' ,') WITHIN GROUP (ORDER BY  Designation ASC)  As 'Parts'
    
    from (
    Select distinct ProjectN,Designation 
    from test
    ) t
    group by ProjectN 
    
    drop table test

    Friday, January 3, 2020 7:30 PM
    Moderator
  • Hi qwertyess, 

    Please check Jingyang Li's script .Also , I change your script and please check .

    ; with cte as (
    SELECT 
      distinct proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name 
      ,daiPaiPro.Quantity*ProShip.Weight 'Weight'
      ,ProShip.Designation 
      ,[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
      Where MONTH([DateMAD])=@month and YEAR([DateMAD])=@year
    )
    SELECT 
       proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name as 'Customer'
      ,sum(Weight)as 'Weight'
      ,STRING_AGG( ISNULL(ProShip.Designation , ' '), ' ,') WITHIN GROUP (ORDER BY ProShip.Designation ASC)  As 'Parts'
      ,[DateMAD] as 'DateMAD'
      ,[Note] 
      ,[CreationDate] 
    FROM cte
    Group By
       proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name 
      ,[DateMAD] 
      ,[Note]
      ,[CreationDate] 
    Order By [DateMAD]

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 6, 2020 3:02 AM
  • Theoretically, it is probably possible to write a custom aggregate function in C#, VB or C++ (if it is worth the effort):

     

     

    Monday, January 6, 2020 6:26 AM
  • Sorry for the late reply I was so sick.

    I try to use your query but i get this error

    Msg 4104, Level 16, State 1, Line 30
    The multi-part identifier "proinfo.ProjectN" could not be bound.
    Msg 4104, Level 16, State 1, Line 31
    The multi-part identifier "proinfo.ProjectName" could not be bound.
    Msg 4104, Level 16, State 1, Line 32
    The multi-part identifier "cust.Name" could not be bound.
    Msg 4104, Level 16, State 1, Line 20
    The multi-part identifier "proinfo.ProjectN" could not be bound.
    Msg 4104, Level 16, State 1, Line 21
    The multi-part identifier "proinfo.ProjectName" could not be bound.
    Msg 4104, Level 16, State 1, Line 22
    The multi-part identifier "cust.Name" could not be bound.
    Msg 4104, Level 16, State 1, Line 24
    The multi-part identifier "ProShip.Designation" could not be bound.
    Msg 4104, Level 16, State 1, Line 24
    The multi-part identifier "ProShip.Designation" could not be bound.

    Then I replaced the table name with cte the query run without any error but unfortunately i get all value (duplicate) any suggestion.


    • Edited by qwertyess Friday, January 10, 2020 8:15 AM
    Friday, January 10, 2020 8:13 AM
  • Sorry for the late reply I was so sick.

    Believe me, I've tried, but all my attempts didn't work.

    Friday, January 10, 2020 8:19 AM
  • Hi,

    Thank you for your reply. 

    Could you please share us  your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 10, 2020 8:58 AM
  • Rachel's cte should should use GROUP BY, not DISTINCT and it should SUM the Weights.  And the GROUP BY clause in the cte should include ProShip.Designation.  That's because when you compute the weights without summing them, you can have one Designation value with multiple Weight values, do when you do DISTINCT you get multiple occurrences of the same Designation and that leads to duplicates in the STRING_AGG.  So I think you want

    ; with cte as (
    SELECT 
      proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name 
      ,SUM(daiPaiPro.Quantity*ProShip.Weight) 'Weight'
      ,ProShip.Designation 
      ,[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
      Where MONTH([DateMAD])=@month and YEAR([DateMAD])=@year
    Group By
       proinfo.ProjectN 
      ,proinfo.ProjectName
      ,cust.Name 
      ,[DateMAD] 
      ,[Note]
      ,[CreationDate]
      ,ProShip.Designation
    )
    SELECT 
       cte.ProjectN 
      ,cte.ProjectName
      ,cte.Name as 'Customer'
      ,sum(Weight)as 'Weight'
      ,STRING_AGG( ISNULL(cte.Designation , ' '), ' ,') WITHIN GROUP (ORDER BY cte.Designation ASC)  As 'Parts'
      ,[DateMAD] as 'DateMAD'
      ,[Note] 
      ,[CreationDate] 
    FROM cte
    Group By
       cte.ProjectN 
      ,cte.ProjectName
      ,cte.Name 
      ,[DateMAD] 
      ,[Note]
      ,[CreationDate] 
    Order By [DateMAD]
    Tom
    • Marked as answer by qwertyess Friday, January 10, 2020 10:43 AM
    Friday, January 10, 2020 9:11 AM
  • Thank you very much Tom and thanks for the valuable information.
    Thanks to everyone who contributed
    Friday, January 10, 2020 10:49 AM