Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Question String data concatenation

More
05 Sep 2019 13:37 - 05 Sep 2019 13:38 #1 by nada
String data concatenation was created by nada
Hello ,

I would like to concatenate data from a same column of a database table .
example :



For the same field i concatenated all the descriptions related to it .

How can I do this with stambia in a staging table ?

Thank you.
Attachments:
Last edit: 05 Sep 2019 13:38 by nada.
More
10 Dec 2020 18:40 #2 by cbouin
Replied by cbouin on topic String data concatenation
Hello, I'm facing the same problem. Did you manage to concatenate your data? How?
More
24 Aug 2021 13:00 #3 by cbouin
Replied by cbouin on topic String data concatenation
Hello,

I was facing the same issue.
I was using Microsoft SQL Server, which does not handle such transformations. A solution could be to use staging tables whith one columns mapped to the "STUFF" function, and a subquery using Xml instruction.

It would look like this:
STUFF( 
  select concat(' - ',temp_table.typecomposition_fr, ': ', temp_table.composition) 
  from stage_plm as temp_table 
  where 
   temp_table.idsap = stage_plm.idsap 
   and temp_table.codesaison = stage_plm.codesaison 
  ORDER BY temp_table.ElementRank ASC 
  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
 ,1,3,'')

Unfortunately, I was retrieving the data from another staging table, with automatic table and column names, so it could not make the job.

The support team gived me another solution: use the internal Stambia H2 database.
It contains a function "group_concat" matching my needs.



It gives my this kind of mapping:
group_concat( 
	concat( ElementTraduction_fr.LibelleCourt, ': ', Composant.composition)
	order by ReferentielElement.Ordre
	separator ' - ' 
)
Attachments: