SDU Tools: Script SQL Server Table as T-SQL Unpivot

Many of my friends have told me that they like how concise the PIVOT and UNPIVOT operators are, but they find the syntax confusing. I must admit that I wasn’t a big fan of these when they were added in SQL Server 2008 as they didn’t really add anything to the language that I couldn’t have done with CASE statements, etc. and there were so many other parts of the language that needed work.

But they are part of T-SQL and it’s useful to be able to use them. To make it easier to get started with the UNPIVOT syntax (the harder one), we added a procedure ScriptTableAsUnPivot to our free SDU Tools for developers and DBAs.

The procedure takes a bunch of parameters and is very, very flexible. It can script either a table or a view from the source table, and can rename it and can change the output schema, along with formatting options:

@SourceDatabaseName sysname -> Database name for the table to be scripted @SourceTableSchemaName sysname -> Schema name for the table to be scripted @SourceTableName sysname -> Table name for the table to be scripted @OutputViewSchemaName sysname -> Schema name for the output script (defaults to same as existing schema) @OutputViewName sysname -> View name for the output script (defaults to same as existing table with _Unpivoted appended) @IsViewScript bit -> Is a view being created? If not, a query is created. (defaults to query) @IncludeNullColumns bit -> Should columns whose values are NULL be output? (defaults to no) @IncludeWHEREClause bit -> Should a WHERE clause be included (when output is not to a view)? (defaults to no) [NOTE: Added in v15] @ColumnIndentSize -> How far should columns be indented from the table definition (defaults to 4) @ScriptIndentSize -> How far indented should the script be? (defaults to 0) @QueryScript nvarchar(max) OUTPUT -> The output script

In the main image above, you can see the output from executing the following script:

Note that in upcoming V15, the ID is always output with the column name AttributeID rather than the original name. If there are multiple primary key columns, they will be called AttributeID, AttributeID2, AttributeID3 and so on.

You can see the procedure in action in the image above, and in the video here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

2019-06-05