SQL

Update: New Stuff from the latest update will be in RED. This list will grow as I find new tools. So if you know of some not on this list do post them in the comments. SQL Server Management Studio Add-in's SSMS Tools Pack - an add-in with lots of IDE options (Query Execution history, regions, debug sections, CRUD stored procedures creation, new query templates, running custom scripts from Object explorer's context menu) for SQL Server Management Studio and SQL Server Management Studio Express Data Scripter - generates insert statements for a table Free SQL Server tools that might make your life a little easier

I have seen a common requirement where we have numbers and alphabets mixed up in a column (house no in the address field) and the number has to be extracted out of the string. Here's a query to do so: -- This query is a Modification of the Original query by 'usenetjb' DECLARE @NumStr varchar(1000)SET @NumStr = 'This string contains 5 words and now 9 words'; 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:

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. SQL-Server Blog of Ritesh Shah --Fight the fear of SQL with SQLHub.com: Generic stored procedure for PIVOT in SQL Server

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.

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. Crosstab Pivot-table Workbench

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!

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 declare @sql varchar(8000) Dynamic Crosstab with multiple PIVOT Columns - Madhivanan

001.CREATE PROCEDURE [dbo].[genericCrosstab] 007. @tableSpec NVARCHAR(4000) = '', 013. @sqlSelect NVARCHAR(512), A generic cross tab / pivot table query for Transact SQL (with sortable columns) [sql] [sql_server] [sql-server] [SQLServer]