Answered by:
UnPivot in the foreach loop container

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
-
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).aspxRegards,
Mike YinIf 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
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
- Edited by Gilles Machetto Tuesday, March 11, 2014 4:26 PM
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).aspxRegards,
Mike YinIf 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