locked
UnPivot in the foreach loop container RRS feed

  • Question

  • Hi

    I would like to "unpivot" SEQUENTIALLY each row in a table

    Before the pivot task in the foreach loop container how to read sequentially each row ?

    1/ from a PK ?

    2/ from parameters with a WHERE field1 = ? and field2 = ? and field3=?....

    These parameters have been set up prevoiouly in the loop

    3/ Others ? ...

    Thx for your help

    Tuesday, March 11, 2014 3:24 PM

Answers

All replies

  • what do you mean by UNPIVOT sequentially? By default when you unpivot, value gets retrieved in the order of columns (ordinal position) in the main table.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Tuesday, March 11, 2014 4:07 PM
  • From this table :

    Field1   Field2   Field3

    A           B           C

    D           E           F

    I would like to read row after row to get this

    A

    B

    C

    D

    E

    F

    Thx


    Tuesday, March 11, 2014 4:24 PM
  • Do you've a PKf for your table? If yes you can simply order by it first followed by default ordering of UNPIVOT (ordinal number you set inside output column propertes)

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Mike Yin Wednesday, March 19, 2014 2:30 AM
    Tuesday, March 11, 2014 4:29 PM
  • Hi Gilles,

    To achieve your goal, you can simply use the Unpivot Transformation or UNPIVOT statement in T-SQL.
    If you want to use Unpivot Transform, it should be configured as follows:

    If you want to use T-SQL, the query is like below:

      SELECT u.Name, u.Category
      FROM [dbo].[MyTable]
      UNPIVOT
      (Name for Category in (Field1, Field2, Field3)) u

    References:

    http://dinesql.blogspot.in/2011/08/pivot-and-unpivot-integration-services.html
    http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support


    • Edited by Mike Yin Wednesday, March 19, 2014 2:54 AM typo
    • Marked as answer by Gilles Machetto Wednesday, March 19, 2014 5:36 AM
    Wednesday, March 19, 2014 2:53 AM
  • Thx Mike !
    Wednesday, March 19, 2014 5:36 AM