I have a task which require the data manipulation to transpose the rows to columns. The data is stored in SQL Server, typical relational database model. I know it can be done with TSQL, but it is so complex that there are almost ten different row groups to be transposed to about 200 columns.
Just wondering whether there is other better tools to do that?
You will have to generate dynamic SQL to do this. Someone asked something similar a couple days ago:
Without a detailed question, it's hard to say which way is the best. However, you can turn rows to columns in TSQL using PIVOT. You may want to check it out and see if it can do what you need.
Are you using SQL Server 2005+? I have a stored procedure that makes it pretty easy to pivot data like that.
This post as an example of using it: http://stackoverflow.com/questions/1032640/sql-query-result-monthly-arrangement
For 200 columns, you should probably break it up into logical groups and store the pivoted data in temp tables (the stored proc does that as an option) - then join up the temp tables for your final result. I've had squirrely results with more than 147 columns with PIVOT.
Some example set up source can be found here.
Ron
I would also try to do it from the application as this might work faster than pivoting the data. But it is one of those things you probably need to try both ways to really see waht is best in your own particular case.