Free SQL Server tools that might make your life a little easier. 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 Create Database Snapshot - simple GUI for creating a database snapshot. Extract Numbers from a String using Sql Server 2005. 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'; BEGINWHILE PATINDEX('%[^0-9]%',@NumStr)> 0SET @NumStr = REPLACE(@NumStr,SUBSTRING(@NumStr,PATINDEX('%[^0-9]%',@NumStr),1),'')END PRINT @NumStr Did you like this post? About The Author. 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. 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. 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. 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. 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. 069. 071. 075. 077. 081.