1

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
user3463579
  • 95
  • 1
  • 8
  • What you are doing violates 1NF by storing multiple values in a single tuple. This is not a pivot which would generate multiple columns. You are trying to generate a delimited list. Unless you have a really good reason for that I would suggest you keep your data normalized. – Sean Lange Feb 26 '18 at 15:36
  • you could use script component, see this [post](https://stackoverflow.com/questions/4726858/ssis-transformation-almost-like-a-pivot) – hkravitz Feb 26 '18 at 15:47

3 Answers3

1

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

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
NateO
  • 3
  • 3