CODEWITHSUNDEEP
sql-serverssissql-server-2014etlssis-2014
user3463579
user3463579

Reputation: 105

Transpose rows of string values to one column in a dataflow with SSIS

I need to transpose some strings concatenated in one column in a Dataflow but I don't find a suitable solution.

I have millions rows like this :

Col1    Col2    Col3
1   MyVal1  String1
1   MyVal1  String2
2   MyVal2  String2
2   MyVal2  String1
2   MyVal2  String3
3   MyVal3  String3
3   MyVal3  String4

I need to transpose these lines like this :

Col1    Col2    Description
1   MyVal1  String1,String2
2   MyVal2  String2,String1,String3
3   MyVal3  String3,String4

I tried with the pivot transformation but it seems not appropriate for this problem.

How can i do that please ?

Thank you for your help.

Upvotes: 1

Views: 713

Answers (3)

NateO
NateO

Reputation: 3

You could try the string_agg function as well.

select
b.col1
, b.col2
, string_agg(b.col3,', ')
from(
select a.col1,a.col2,a.col3
from table as a
) as b
group by a.col1,a.col2

Upvotes: 0

Hadi
Hadi

Reputation: 37313

If the source is an SQL table you can do this using a similar query:

Select Col1,Col2,  
        STUFF((SELECT ', ' + Col3
                   FROM Table1 AS T3 
                   WHERE T3.Col1 = Table1.Col1 
                   FOR XML PATH('')), 1, 2, '')
FROM Table1
GROUP BY Col1,Col2

In the OLEDB Source, select the source type as SQL Command and write the following command.

Note: if the data is not stored in SQL, you can store it in a staging table and perform this query. Else you have to write your own logic in a script component transformation.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

If you need to do this in the dataflow, you can do it with a script transformation. Just pre-sort your data by Columns 1 & 2, and then keep appending to a string variable for Column 3 until the value of 1 or 2 changes.

Upvotes: 0

Related Questions