locked
Pivot unknown number of values into id and delimited values list RRS feed

  • Question

  • I would like to pull data from this type of structure:

    create table user_favorite_colors ( user_id int, color_name varchar(100))

    which contains data like:

    user_id color_name
    1 Blue
    1 Red
    2 Green

    into something like this:

    user_id colors
    1 Blue,Red
    2 Green

    Is there a way to do this that doesn't involve writing my own script component or creating a custom dataflow task?

    Tuesday, April 5, 2011 5:05 PM

Answers

  • Sorry about the second link.

    So, you already have the data in the table, just start creating the package and use the Pivot Transformation, follow yet another link which I think is pretty close to what you've got:

    http://www.proteanit.com/b/2007/08/02/ssis-pivot-component/


    Arthur My Blog
    By: TwitterButtons.com
    • Proposed as answer by Jerry Nee Friday, April 8, 2011 9:18 AM
    • Marked as answer by Jerry Nee Wednesday, April 27, 2011 10:23 AM
    Tuesday, April 5, 2011 7:00 PM

All replies

  • Use the Pivot Transformation Task.

    You may use Reza's "Complete Tutorial for the Pivot Task" at http://goo.gl/FKRws


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, April 5, 2011 5:20 PM
  • I don't see how the pivot task can be used to do what I want without putting every single color in the output list and then concatenating them together in another task.
    Tuesday, April 5, 2011 5:39 PM
  • You should be able to.

    See if this http://www.bimonkey.com/2009/06/the-pivot-transformation/ is helpful or this  http://sqljunkies.com/Article/705F07C3-69FE-4CAF-8CF8-CADBF145F372.scuk

     


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, April 5, 2011 6:36 PM
  • The second link doesn't seem to be valid and the first one seems to be pointing out that you must define the pivot key values for the pivot task to work.  The only way I know how to get what I want with T-SQL is to dynamically generate the pivot statement by first getting a distinct list of values and then running it as dynamic SQL.
    Tuesday, April 5, 2011 6:46 PM
  • Sorry about the second link.

    So, you already have the data in the table, just start creating the package and use the Pivot Transformation, follow yet another link which I think is pretty close to what you've got:

    http://www.proteanit.com/b/2007/08/02/ssis-pivot-component/


    Arthur My Blog
    By: TwitterButtons.com
    • Proposed as answer by Jerry Nee Friday, April 8, 2011 9:18 AM
    • Marked as answer by Jerry Nee Wednesday, April 27, 2011 10:23 AM
    Tuesday, April 5, 2011 7:00 PM
  • select
        user_id,
        stuff((
            select ',' + t.[color_name]
            from pv t
            where t.user_id = t1.user_id
            order by t.[color_name]
            for xml path('')
        ),1,1,'') as name_csv
    from pv t1
    group by user_id

    Thanks http://dwhanalytics.wordpress.com/


    • Edited by _AKS Monday, April 11, 2011 12:40 PM space
    Monday, April 11, 2011 12:39 PM