Many times we encounter a scenario where we need to transpose rows data as a column in SQL Server. In Excel it is quite easy, just copy the data and paste as Transpose, but in SQL server we have various methods by which we can achieve same results. Consider a scenario, where we have a table "transactions" which give datewise data about how many transactions were done in a country and total amount value.
For achieving above result we have various options available to us in SQL Server:
| country | totalCount | charDate | totalAmount |
| Australia | 36 | 12-Jul | 699.96 |
| Australia | 44 | 12-Aug | 1368.71 |
| Australia | 52 | 12-Sep | 1161.33 |
| Australia | 50 | 12-Oct | 1099.84 |
| Australia | 38 | 12-Nov | 1078.94 |
| Australia | 63 | 12-Dec | 1668.23 |
| Austria | 11 | 12-Jul | 257.82 |
| Austria | 5 | 12-Aug | 126.55 |
| Austria | 7 | 12-Sep | 92.11 |
| Austria | 12 | 12-Oct | 103.56 |
| Austria | 21 | 12-Nov | 377.68 |
| Austria | 3 | 12-Dec | 14.35 |
Now if we want data in the below format, where we can see datewise data per country
| chardate | AustraliaTotal $ Amount | Australia # of Transactions | AustriaTotal $ Amount | Austria # of Transactions |
| 12-Jul | 699.96 | 36 | 257.82 | 11 |
| 12-Aug | 1368.71 | 44 | 126.55 | 5 |
| 12-Sep | 1161.33 | 52 | 92.11 | 7 |
| 12-Oct | 1099.84 | 50 | 103.56 | 12 |
| 12-Nov | 1078.94 | 38 | 377.68 | 21 |
| 12-Dec | 1668.23 | 63 | 14.35 | 3 |
For achieving above result we have various options available to us in SQL Server:
1st Option: USE PIVOT
The simplest way to transpose rows into columns, we can use below Script:
The PIVOT function would likely have a performance impact on the query based on the complexity of the pivot and/or the size of the data being transformed. This performance impact may be less than you might imagine if appropriate filters are applied. At any rate, performance testing would be critical, whether you use the PIVOT function or implement equivalent functionality in the business layer. A well-constructed PIVOT statement could lessen the chances of downstream bugs, as the code required to pivot outside of SQL could be very complex, especially if NULLs are allowed in the key fields.
2nd Option: USE Cursor
Cursors give us another option for transposing rows to the column, even though common belief is that we should not use a cursor, but in some cases, use of cursor is recommended, otherwise, Microsoft would have deprecated it by now. Below script shows how we can use cursor for transposing rows to columns
The major limitation of transposing rows into columns using CURSOR is a disadvantage that is they come at a notable performance cost. This is because the Cursor generates a separate query for each FETCH NEXT operation. Because of multiple queries generated per FETCH NEXT operation, it is not surprising to observe that the IO statistics of the CURSOR usage is largely spent storing the output of the FETCH NEXT operations in an internal table – Worktable. Reminder that with all its high-maintenance requirement, the PIVOT version of the script never temporarily stored data in Worktables
3rd Option: USE XML
The XML option to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation. The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in functions (i.e. STUFF, QUOTENAME). Below script will show how we can use XML for transposing
In terms of IO, the statistics of the XML version of the script is almost similar to the PIVOT – the only difference is that the XML has a second scan of myTransactions table but this time from a logical read - Data Cache
4th Option: USE Dynamic SQL
Alternative of using XML option to transposing rows into columns is using purely dynamic SQL. This option utilizes same built-in functions that are used in XML version. Sample script is as below
In this article, we’ve had a look at available options to transposing rows into columns in T-SQL. The PIVOT option is the simplest option with a less expensive query plan but its capabilities were limited when it came to addressing dynamic column expansion. We then looked at the CURSOR as a possible option to transposing rows into columns and found that it addressed all the limitations that were found in the PIVOT yet its ability to generate different query plans for every FETCH NEXT operation was its very own performance disadvantage. Finally, the XML and the Dynamic SQL options proved to be the best optimal options in terms of transposing rows into columns with favorable performance results and effective handling of vertical and horizontal expansions
In this article, we’ve had a look at available options to transposing rows into columns in T-SQL. The PIVOT option is the simplest option with a less expensive query plan but its capabilities were limited when it came to addressing dynamic column expansion. We then looked at the CURSOR as a possible option to transposing rows into columns and found that it addressed all the limitations that were found in the PIVOT yet its ability to generate different query plans for every FETCH NEXT operation was its very own performance disadvantage. Finally, the XML and the Dynamic SQL options proved to be the best optimal options in terms of transposing rows into columns with favorable performance results and effective handling of vertical and horizontal expansions
Comments
Post a Comment