Best technique to create a text string from rows?

  • 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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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