Transposing rows and Columns in SQL
-
I have a MQ-SQL 2005 stored procedure that returns a dataset that is fine except I would like to change the rows to the columns and the columns into the rows. Is there an easy command to do this, or is pivot the only option (which is not easy)? Thanks,
Leo T. Smith Program/Analyst Supervisor
-
I have a MQ-SQL 2005 stored procedure that returns a dataset that is fine except I would like to change the rows to the columns and the columns into the rows. Is there an easy command to do this, or is pivot the only option (which is not easy)? Thanks,
Leo T. Smith Program/Analyst Supervisor
Hi, This kind of problems are called Cross Tab Queries and can be solved easily using PIVOTING (a feature from SQL SERVER 2005+). Here is an example Pivots with Dynamic Columns in SQL Server 2005[^] You can even look into a) Cross-Tab reports in SQL Server 2005[^] b)Using PIVOT and UNPIVOT[^] In Sql Server 2000, Pivoting was achieved using Case along with an aggregate function. Look here Pivot Table Data in SQL Server 2000 and 2005[^] However, a few days back I gave a similar kind of answer in Bytes. U can have a look Month to month yearly results[^] Hope this helps :)
Niladri Biswas