SQL

Free SQL Server tools that might make your life a little easier. Extract Numbers from a String using Sql Server 2005. SQL Pivot. How to PIVOT Data Using T-SQL Jonathan Kehayias, March 27, 2008 A common expectation in data extraction is the ability to transform theoutput of multiple rows into multiple columns in a single row.

SQL Server2005/2008 provide the ability to do this with the PIVOT operator in a Query. For example, from the following data: The desired output is: There are several methods to accomplish the desired output. Fight the fear of SQL with SQLHub.com: Generic stored procedure for PIVOT in SQL Server. Well, so far I have written few small example of PIVOTing the data in SQL Server and thought that now this is enough about PIVOT, I won’t write anything more about PIVOT but when I seen one good stored procedure for dynamic PIVOT in expert exchange forum written by my friend and very experienced person Mr.

Mark Wills I tempted again to share PIVOT material with my reader. Let us FIGHT THE FEAR OF PIVOT with SQLHub.com Here is the article written by Mr. Mark Wills. I am sure my blog reader will like his article very much. Another Dynamic SQL CrossTab Stored Procedure. First off, before going any further make sure you have read the hall of fame SQLTeam article by Rob Volk on generating crosstab results using a flexible, dynamic stored procedure that has been viewed over 100,000 times!

This entire concept and pretty much all of the ideas I've had regarding this topic and this techinique in general are all due to Rob's great work and his very clever stored procedure. It must be crosstab season or something, because lately I've been getting quite a few emails and comments about an alternative stored procedure that I've posted in the comments to that article that has been helping quite a few users. To potentially help others out there with this common request (which I still feel should be mostly done at the presentation layer, but I suppose it's not always possible) here's a quick recap/reprint of my adaptation of Rob's excellent idea and some notes.

The main difference between Rob's original stored procedure and mine are that. Crosstab Pivot-table Workbench. There comes a time with many Database Developers charged with doing management reports when the process of doing it properly gets very tedious.

By 'doing it properly', I mean the 'best practice' of having to do the basic reporting in SQL and relying on a front-end application to do the presentation. This is particularly true where the management want simple aggregate reports, or 'Pivot-table' reports. Presentation is so closely tied with the data that splitting the process can sometimes lead to more problems than it solves. Of course, we have Reporting Services, Analysis Services and other external tools, but there are times when a simple solution based in TSQL has the upper hand. Anyone who was weaned on Excel knows that these pivot tables are dead simple.

What they want to see is something like this (using NorthWind so those stuck with SQL 2000 can join in) No. Now, you'll notice that we've taken a bit of trouble to add some formatting. Dynamic Cross-Tabs/Pivot Tables. By Rob Volk on 12 March 2001 | 239 Comments | Tags: Dynamic SQL IMHO, the best feature of MS Access is the TRANSFORM statement, used to create cross-tabs/pivot tables.

It does all of the work of dynamically generating the cross-tabulation and the summary calculations. T-SQL unfortunately doesn't have this statement, so you're stuck using complicated SQL commands, expensive 3rd party products, or exotic OLAP to make pivot tables...or you can use the following procedure to dynamically create them! I got the idea from this question, asking how to "undo" a pivot table, and then I started working on how to create them in T-SQL.

There are numerous ways of doing pivot tables, and this site has several examples (and lots of other cool stuff). Yeah, I know, you want to know how it works :) I won't go into detail on the mechanics of the code. The SELECT statement can be anything, as long as it has proper syntax and includes a GROUP BY clause. I'll list some cross-tab settings and the results.

Dynamic Crosstab with multiple PIVOT Columns - Madhivanan. Jeff Smith in his weblog showed how to generate Crosstab reports using a stored procedure.

It works only for one PIVOT Column.I had a requirement to generate crosstab reports with more than one pivot column. So I used the same approach he used and modified his procedure as shown below CREATE procedure CrossTab (@select varchar(2000),@PivotCol varchar(100), @Summaries varchar(100), @GroupBy varchar(100),@OtherCols varchar(100) = Null) ASset nocount onset ansi_warnings off.

A generic cross tab / pivot table query for Transact SQL (with sortable columns) [sql] [sql_server] [sql-server] [SQLServer] 001.CREATE PROCEDURE [dbo].

[genericCrosstab] 007. @tableSpec NVARCHAR(4000) = '', 013. @sqlSelect NVARCHAR(512), 019. 024. 029. 035. 040. 048. 053. 057. 063. 065.