Concatenate rows with semicolon separator after grouping

Question

I want to concatenate my result with semicolon separator so this is my query

SELECT 
   Concat(Year(info.[CreationDate]),'/',Trim('BS-' from info.ProjectN)) As 'AllProjectN'
  ,part.Designation  As 'AllDesignation' 
  ,cust.Name   As 'AllCustomer'
  ,prod.[Quantity] As 'AllQuantity'
  ,Case when prod.[Quantity] <=3 then prod.[Quantity] 
                   when prod.[Quantity] between 4 and 501 then 3
                   when prod.[Quantity] between 502 and 1201 then 5
                   when prod.[Quantity] between 1202 and 1801 then 8
                   when prod.[Quantity] between 1802 and 3200 then 13
                   else ' ' end as 'Echantillonnage'
  ,[GalvaQualityDailyFicheControle].[CreationDate]

FROM [dbo].[GalvaQualityDailyFicheControle]
Inner Join GalvaQualityDailyProduction prod on prod.id= [GalvaQualityDailyFicheControle].FK_idDailyProduction
Inner join GalvaParts part on part.id=prod.[FK_idPart]
Inner join ProjectInfoGalva info on info.id=part.IdProject
inner Join Customer cust on cust.ID=info.FK_Customer

Where Convert(Date,[GalvaQualityDailyFicheControle].[CreationDate]) = '05-27-2020' AND [GalvaQualityDailyFicheControle].FK_idNextProcess=13
Group By
   Concat(Year(info.[CreationDate]),'/',Trim('BS-' from info.ProjectN)) 
  ,part.Designation 
  ,cust.Name   
  ,prod.[Quantity] 
  ,[GalvaQualityDailyFicheControle].[CreationDate]

I get this result

I try with STRING_AGG I get one row but the data is duplicated

SELECT 
   STRING_AGG(Concat(Year(info.[CreationDate]),'/',Trim('BS-' from info.ProjectN))  , ' ;')As 'AllProjectN'
  ,STRING_AGG(part.Designation  , ' ;')As 'AllDesignation' 
  ,STRING_AGG(cust.Name   , ' ;')As 'AllCustomer'
  ,STRING_AGG(prod.[Quantity] , ' ;')As 'AllQuantity'
  ,STRING_AGG(Case when prod.[Quantity] <=3 then prod.[Quantity] 
                   when prod.[Quantity] between 4 and 501 then 3
                   when prod.[Quantity] between 502 and 1201 then 5
                   when prod.[Quantity] between 1202 and 1801 then 8
                   when prod.[Quantity] between 1802 and 3200 then 13
                   else ' ' end , ' ;')as 'Echantillonnage'
  ,[GalvaQualityDailyFicheControle].[CreationDate]

FROM [dbo].[GalvaQualityDailyFicheControle]
Inner Join GalvaQualityDailyProduction prod on prod.id=[GalvaQualityDailyFicheControle].FK_idDailyProduction
Inner join GalvaParts part on part.id=prod.[FK_idPart]
Inner join ProjectInfoGalva info on info.id=part.IdProject
inner Join Customer cust on cust.ID=info.FK_Customer

Where Convert(Date,[GalvaQualityDailyFicheControle].[CreationDate]) = '05-27-2020' AND [GalvaQualityDailyFicheControle].FK_idNextProcess=13
Group By
  [GalvaQualityDailyFicheControle].[CreationDate]

This is the result I get

How can I get the one row without duplicate the data?.

Please clarify the desired resutls. "one row without duplicate the data" is not clear. — David Browne - Microsoft, May 28 '20 at 16:52
Does this answer your question? [Produce DISTINCT values in STRING\_AGG](https://stackoverflow.com/questions/51646385/produce-distinct-values-in-string-agg) — Marc Guillot, May 28 '20 at 16:53
@DavidBrowne-Microsoft when I run the first query I get two rows and when I run the second query I supposed to get data from first and second row with semicolon as separator but when I run the second query each row duplicate themselves twice(I supposed to get only two values ) — M.Bouabdallah, May 28 '20 at 18:26
@MarcGuillot Thank you very much for your help ,I also got a lots of help from this thread https://stackoverflow.com/a/4473039/9608194 — M.Bouabdallah, May 30 '20 at 13:33

score 0 · Accepted Answer · answered May 30 '20 at 13:37

ALTER Proc [dbo].[RepDailyQualityGalvaFicheControleAll]
@Date date
AS
with myCTE 
(AllProjectN,AllDesignation,AllCustomer,AllQuantity,Echantillonnage,CreationDate)AS
(
SELECT 
   Concat(Year(info.[CreationDate]),'/',Trim('BS-' from info.ProjectN)) As 'AllProjectN'
  ,part.Designation  As 'AllDesignation' 
  ,cust.Name   As 'AllCustomer'
  ,prod.[Quantity] As 'AllQuantity'
  ,Case when prod.[Quantity] <=3 then prod.[Quantity] 
                   when prod.[Quantity] between 4 and 501 then 3
                   when prod.[Quantity] between 502 and 1201 then 5
                   when prod.[Quantity] between 1202 and 1801 then 8
                   when prod.[Quantity] between 1802 and 3200 then 13
                   else ' ' end as 'Echantillonnage'
  ,[GalvaQualityDailyFicheControle].[CreationDate]

FROM [dbo].[GalvaQualityDailyFicheControle]
Inner Join GalvaQualityDailyProduction prod on prod.id=[GalvaQualityDailyFicheControle].FK_idDailyProduction
Inner join GalvaParts part on part.id=prod.[FK_idPart]
Inner join ProjectInfoGalva info on info.id=part.IdProject
inner Join Customer cust on cust.ID=info.FK_Customer

Where Convert(Date,[GalvaQualityDailyFicheControle].[CreationDate]) = @Date AND [GalvaQualityDailyFicheControle].FK_idNextProcess=13
Group By
   Concat(Year(info.[CreationDate]),'/',Trim('BS-' from info.ProjectN)) 
  ,part.Designation 
  ,cust.Name   
  ,prod.[Quantity] 
  ,[GalvaQualityDailyFicheControle].[CreationDate] 
)
 Select STRING_AGG(AllProjectN, ' ;') AllProjectN
  ,STRING_AGG(AllDesignation, ' ;')AllDesignation
  ,STRING_AGG(AllCustomer, ' ;')AllCustomer
  ,STRING_AGG(AllQuantity, ' ;')AllQuantity
  ,STRING_AGG(Echantillonnage, ' ;')Echantillonnage
 ,CreationDate
FROM myCTE
Group By CreationDate

Concatenate rows with semicolon separator after grouping

1 Answers1