0

I need to concatenate the rows(taille). for example, for the code_commande 001 and code_article=1, I need to concatenate in list all taille which have these two conditions. another example, for code_commande=001 and code_article=2, the same job I need to concatenate in a list all taille which have these two conditions. this for all

code_commande   code_article    taille
001                  1          s         
001                  1          m         
001                  1          xl        
001                  1          x52       
001                  2          m         
001                  1          5566      
001                  2          x52       
001                  1          xl        
002                  1          s         
002                  2          m         
001                  3          xxl       
002                  3          xs        
001                  1          ml        
001                  1          xs32

I need to concatenate taille for each code_commande for each code_article example of result:

001            1             s,m,xl etcc

dynamically

I should have a table who grouped the ( taille) for each code_commande for each code_article like:

001                    1                  s,m,xl,
001                    2                  s,xl,l 
002                    1                  xs,ettcc

I have tried this query but ,it concatenate all (taille) for all rows the query

Select  [code_commande],[code_article], SUBSTRING( 
(
     SELECT  ',' +[taille]  AS 'data()' 
         FROM [dbo].[commande] FOR XML PATH('') 
), 2 , 9999) As taille_commande
from [dbo].[commande] 
order by [code_article],[code_commande]desc
Jason Aller
  • 3,541
  • 28
  • 38
  • 38
user11186153
  • 11
  • 3
  • Explain what you have done and why it does not work. – ceving Mar 26 '19 at 09:10
  • i need to have table that's have all (tailles) ordered by code_commande and code article exemple : for code_commande 1 and code_article 1 i need have all tailles concatenated and for code_commande 1 and article 2 i need have all tailles etttc – user11186153 Mar 26 '19 at 09:14
  • Welcome to stackoverflow. Please take a minute to take the [tour], especially [Ask]. Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) – Zohar Peled Mar 26 '19 at 09:16
  • i need a query who returns this – user11186153 Mar 26 '19 at 09:17
  • Well I need a brand new Ferrari. Think we can trade? Stackoverflow is not a free coding service. You are expected to show your efforts at solving the problem at hand. – Zohar Peled Mar 26 '19 at 09:18
  • You can use [`STRING_AGG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017) to achieve this. – Magnus Mar 26 '19 at 09:34

1 Answers1

0

As mentioned, STRING_AGG() is your friend for this requirement. Assuming your original post contained the schema you're working with, a simple aggregate query will give you the results you want.

select code_commande, code_article, STRING_AGG(taille, ',') as taille_commande
from dbo.commande
group by code_commande, code_article

STRING_AGG reference

Note this is only available in SQL Server 2017+ and azure. To see a possible solution for previous versions, see this duplicate.

Tassie Devil
  • 1
  • 1
  • 1