April 2, 2009 at 1:59 pm
I need take a datasource and generate a text file output 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.
Thanks
April 2, 2009 at 8:05 pm
I'll help you with the hard bit. Assume you have a table 'Colour' as follows:
ID ParentID Colour
11 Black
21 Blue
32 White
43 Brown
53 Orange
63 Purple
Try the following SQL:
Select ParentID, Stuff((SELECT ', ' + C1.Colour
FROM Colour C1
WHERE C1.ParentID = C.ParentID
FOR XML PATH ('')),1,1,'') AS Colour
From Colour C
Group By ParentID
Order By ParentID
And the following results will be obtained:
1 Black, Blue
2 White
3 Brown, Orange, Purple
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 3, 2009 at 7:06 am
Thank you. Seems to work well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply