I need to concat data of columns of rows that have same comparing column values

Question

Peruka, Sikander reddy 155

I need to concatenate data of columns of rows that have same comparing column value.

--Input Data

Number Code

100 A

100 B

100 C

200 X

200 Y

--Output Data

Number Code

100 A|B|C

200 X|Y

Peruka, Sikander reddy 155 Reputation points

2024-03-29T21:24:00.8066667+00:00

We are using serverless sql pool and need to implement this logic with in dataflow
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-04-02T05:18:00.3466667+00:00

@Peruka, Sikander reddy ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Accepted answer

1 additional answer

Peruka, Sikander reddy 155 Reputation points

2024-03-29T21:24:00.8066667+00:00

We are using serverless sql pool and need to implement this logic with in dataflow
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2024-04-02T05:18:00.3466667+00:00

@Peruka, Sikander reddy ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding , you are trying to perform data transformation using mapping dataflow.

Kindly follow the below steps to achieve the above requirement:

User's image

User's image

Data preview: User's image

Add derived column transformation and use below expression to convert array objects ["A","B","C"] into A|B|C :

ltrim(toString(reduce(Code,'', #acc + '|'+ #item, #result)),'|')

User's image

Data preview: User's image

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

Peruka, Sikander reddy 155 Reputation points

2024-04-02T15:05:17.0133333+00:00

Thanks a lot and this helped!!!

Answer 2

Erland Sommarskog 121.9K MVP Volunteer Moderator

Use the STRING_AGG function:

SELECT Number, STRING_AGG(Code, '|') WITHIN GROUP (ORDER BY Code)
FROM   tbl
GROUP BY Number

This works on a dedicated pool (I just tested). I cannot vouch for that it will work in a serverless pool.

Peruka, Sikander reddy 155 Reputation points

2024-03-29T21:27:56.9533333+00:00

We are using serverless sql pool and also need the logic in dataflow, Thanks