TSQL - Transforming rows to columns and columns to rows

Transforming rows to columns (PIVOT/CROSS TAB) and columns to rows (UNPIVOT) may be one of the common requirements that all of us must have seen several times in our programming life. SQL Server 2005 introduced two new operators: PIVOT and UNPIVOT that made writing cross-tab queries easier.

I have posted an example that transforms rows to columns using the PIVOT operator here: http://blog.sqlserver.me/2008/09/pivot-cross-tab-sql-server-2005.html

PIVOT operator is available only in SQL Server 2005/2008. It does not exists in SQL Server 2000. I have posted another version of the above sample code that works in SQL Server 2000. You can find the post here: http://blog.sqlserver.me/2008/09/pivot-cross-tab-sql-server-2000.html

I have posted one more example, that performs the reverse operation (columns to rows) using UNPIVOT. You can find it here: http://blog.sqlserver.me/2008/09/unpivot-sql-server-2005.html

Continue reading “TSQL - Transforming rows to columns and columns to rows”  »»

Be Careful while renaming stored procedures, views, functions and triggers.

I suppose, many of us have renamed stored procedures, views, functions and triggers several times in the past. Sometimes renaming such objects can give you some trouble. For example, after you rename an object, sp_helptext or OBJECT_DEFINITION will still give you the object definition with the previous name. This can cause problems some times, especially if you use sp_helptext to retrieve the object definition and make modifications to the procedure/function/trigger/view body.

I have posted my findings about this here: http://blog.sqlserver.me/2008/09/be-careful-when-renaming.html

Continue reading “Be Careful while renaming stored procedures, views, functions and triggers.”  »»