I have the following Source table and I need to load the Destination table further below. Both tables are SQL Server 2008 R2. But I’m struggling to work out a solution. I’d really appreciate if someone would point me in the right direction. Source table: RecordNo FieldName FieldValue 1 Field1 xxxx 1 Field2 111 1 Field3 yyy 2 Field1 aaaa 2 Field2 222 2 Field3 yyy I have three distinct values of FieldName in the above example, however the actual source table has 309 distinct values of FieldName. Every distinct RecordNo has 309 rows. Destination table: RecordNo Field1 Field2 Field3 1 xxxx 111 yyy 2 aaaa 222 yyy How do I get from the Source to the Destination? At the risk of overcomplicating my post, here are some of my attempts/thoughts at resolving this: 1. Pivot Transform The Pivot transform would require me to manually enter all 309 FieldName values … surely there is a better way than this. 2. http://blog.programmingsolution.net/sql-server-2008/sql-server-pivot-converting-rows-to-columns-with-dynamic-query/ This blog post shows how to use Transact-SQL to convert row values into column names. I’ve got this Transact-SQL working successfully for my source table in SQL Server Management Studio. However, when I then put this code into an OLE DB Source, with Data Access Mode of SQL Command, I get an error “No column information was returned by the SQL Command”. I’ve spent a fair bit of time trying to working out why – but I’ve hit a brick wall. 3. http://agilebi.com/jwelch/2007/09/14/dynamically-pivoting-rows-to-columns/ This forum post shows how to use VB script to pivot rows into columns – but it doesn’t show how to pivot row values into column names. I’m not strong at VB scripts, so I don’t know how to extend this code. If extending this VB script is the answer, then I’ll learn, but I’d like to know that it is right direction to take. 4. Numerous other forum and blog searches, which I won’t bore you with. Thanks in advance.

if your source and destination tables are both SQL Server 2005 and above, I strongly recommend to use PIVOT t-sql method for better performance. this will led result you want: select RecordNo,[Field1] as Field1,[Field2] as Field2,[Field3] as Field3 from ( select RecordNo,FieldName,FIeldValue from SourceTable ) as p pivot ( max(FieldValue) for FieldName in ( [Field1],[Field2],[Field3]) )as pvt but if you want to do pivot for any source you can use PIVOT Transformation in the SSIS as below tutorial: http://www.rad.pasfu.com/index.php?/archives/14-PIVOT-Transformation-SSIS-Complete-Tutorial.htmlhttp://www.rad.pasfu.com

Thanks Reza, I have looked at t-sql Pivot extensively and understand how it works. My challenge is that I have 309 distinct values in FieldName that I need to turn into 309 column names. To use the t-sql Pivot I would need to explicitly list all 309 in the t-sql – which I’m trying to avoid. http://blog.programmingsolution.net/sql-server-2008/sql-server-pivot-converting-rows-to-columns-with-dynamic-query/ does show me how to use t-sql Pivot without having to explicitly list all 309 FieldName values. To that end I have written the following t-sql. declare @FieldList as varchar(max) select @FieldList = stuff( (select ',[' + FieldName + ']' from SourceTable where RecordNo = 1 for xml path('')) ,1,1,'') declare @dynamic_pivot_query as varchar(max) set @dynamic_pivot_query = 'select RecordNo, ' + @FieldList + ' from (select RecordNo , FieldName , FieldValue from dbo.SQL_WRK_Source_Data) as s pivot (max(FieldValue) for FieldName in (' + @FieldList + ')) as p' exec(@dynamic_pivot_query) This works perfectly when I run it in Management Studio. But it does not work when I enter it into the OLE DB Source Editor/Data Access mode SQL Command. I’m getting “No column information was returned by the SQL command.” On further investigation SSIS doesn’t like the following part of the code: + @FieldList + If I replace + @FieldList + with some actual FieldName values then it works. Any thoughts on why this t-sql code works in Management Studio, but not in the OLE DB Source Editor? Or any suggestions on alternative ways to resolve this? Thanks in advance.

If you want to do a DYNAMIC PIVOT, you can not use data flow because data flow doesn't support dynamic metadata. you can do it with Execute SQL Task in control flow, note that you should add an INSERT INTO line before selecting results with dynamic pivot or if you want to load all data into a new table you can use Select Into command let me know where you need more details.http://www.rad.pasfu.com

Of course! All is working now. Thanks Reza.

If you above solution is not working then have look this article very well explained ...... http://www.ashishblog.com/blog/how-to-convert-rows-into-columns-using-pivot-in-sql-serverquery/