Search This Blog

September 13, 2013

SQL Query: Transpose Columns to Rows via UNPIVOT

In this sprint, we have new features and I need to validate the data accuracy in our Silverlight Application and some related tables in SQL Server 2008 R2.

A table has 300+ columns

B table has 200+ columns.

The new features of Silverlight application are still in Development and automation is not possible at this moment.

If I would like to validate data accuracy in many columns in the tables, after running SQL query via SSMS, I need to use the scrollbar at the bottom to switch to the right to take a look at the values gradually. That’s not efficient.

One efficient way is to use “copy with headers” to copy the headers & values and past the data into the Excel Spreadsheet. After that, I can compare the values between Silverlight UI and the spreadsheet quickly after using transpose function in Excel. If I can transpose columns to rows in SQL query directly, that’s great too.

The following one is the simple SQL query for me to transpose columns to rows easily via UNPIVOT.

See Also

A basic SQL query to validate the distance via two Latitude / Longitude Points

1 comment:

Sunil Bailwal said...

Thanks, very helpful to me.