April 2, 2009 at 2:31 pm
I posted this in the SSIS forum, not trying to dupe post but I am looking to find an SSIS and T-SQL approach (which may be the same). I wanted to post it here as I will likely need to generate data in this format for some screen presentation/reports and therefore will likely need to do this in T-SQL as well as SSIS.
I need take a datasource and generate a text file output (or screen/report/etc) and I am trying to determine the best approach. This may be an SSIS transform or a SQL statement for the solution. The data source consists of two tables. The first is a parent table with basic user information. ID, Name, etc. The second table contains childern records tied by ID. The resulting output needs to be one row where all the children records are a delimited set of values.
So for example given this source data
Table1 (id, first, last):
1 Bob Smith
2 Mr. Jones
3 Jill Smith
Table 2 (id, fk to table 1 id, value):
1 1 Blue
2 1 Black
3 2 White
4 3 Orange
5 3 Purple
6 3 Brown
The resulting text file would be someting like:
"Bob", "Smith", "Blue, Black"
"Mr.", "Jones", "White"
"Jill", "Smith", "Orange, Purple, Brown"
The child table is a zero to many relationship. What is the best way (best way is most efficient, but open to easiest method as well) to essentially pivot the child data into a single text file column output? I am open to SSIS or T-SQL solutions.
April 2, 2009 at 3:22 pm
Hi
As I learned by my self (thanks to Barry 😉 ) the best way would be a combination of a xml transformation and stuffing of the result:
DECLARE @t1 TABLE (id INT, fname VARCHAR(100), lname VARCHAR(100))
INSERT INTO @t1
SELECT 1, 'Bob', 'Smith'
UNION SELECT 2, 'Mr.', 'Jones'
UNION SELECT 3, 'Jill', 'Smith'
DECLARE @t2 TABLE (id INT, t1id INT, color VARCHAR(100))
INSERT INTO @t2
SELECT 1, 1, 'Blue'
UNION SELECT 2, 1, 'Black'
UNION SELECT 3, 2, 'White'
UNION SELECT 4, 3, 'Orange'
UNION SELECT 5, 3, 'Purple'
UNION SELECT 6, 3, 'Brown'
SELECT t1.fname, t1.lname, STUFF((SELECT ', ' + color FROM @t2 t2 WHERE t1.id = t2.t1id FOR XML PATH('')), 1, 2, '')
FROM @t1 t1
Greets
Flo
April 3, 2009 at 7:06 am
Thanks. This seems to work well with the small test data I have setup.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply