CODEWITHSUNDEEP
sql-serverssis
marco
marco

Reputation: 138

Concat multiple rows in SSIS in one column

I have two tables:

Table 1 (Name, (...), ProductID)

Table 2 (Customer Name, ProductID)

Sample Table 1

Test1 | 1

Sample Table 2:

Customer1 | 1

Customer2 | 1

The table schema is fixed and I cannot change it and I cannot create additional views, etc. With SQL Server Integration Services I have to create a new table. Within this new table I need to have one column with the customers like Customer1; Customer2 for Test1.

I know that I could do it with COALESCE, but I have no idea how to do that within SSIS. Which transformation should I use, and how?

Update Here is the sample for COALESCE:

DECLARE @names VARCHAR(150) 

SELECT @names = COALESCE(@names + '; ', '') + [CustomerName]
  FROM Table2

SELECT @names

How to insert this snippet into one new SELECT * FROM Table1?

Upvotes: 1

Views: 3081

Answers (3)

marco
marco

Reputation: 138

Ok, I've found the solution:

SELECT [All_My_Other_Fields], STUFF(
             (SELECT '; ' + [CustomerName] 
              FROM Table2
              WHERE Table2.ProductID=Table1.ProductID
              FOR XML PATH (''))
             , 1, 1, '') AS Customers
FROM Table1

Thank you all for your help!

Upvotes: 1

Zane
Zane

Reputation: 4169

Take your OLEDB source and use a SQL query for that source. 

SELECT T1.Name, T2.Customer  
FROM [TABLE 1] AS T1
    INNER JOIN [Table 2] as T2
        ON T1.ProductID = T2.ProductID

Then use a data flow to move that to your OLEDB destination. No need to get fancy with SSIS on this if you can easily make the database engine handle it.

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31795

Your best, and most performant, solution would be to use a SQL Query as your Source, instead of the raw tables. Then you can do the COALESCE (or Concatenation) in the SQL query and it will be passed through the SSIS data pipe.

Upvotes: 1

Related Questions