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 Sort SQL Project Files - sorts SQL project files The Black Art of Writing a SQL Server Management Studio 2005 Add-In - a how-to article and a Search DB Objects add-in with code How to create SSMS Add-in - an article that describes how to create add-ins without any source code Free SQL Server books "Boost your DBA expertise" and "DBA Best Practices" - two free books by RedGate Samples.
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. Create Sample Data -- Suppress data loading messages SET NOCOUNT ON -- Create Sample Data using a Table Varable DECLARE @OrderDetail TABLE (orderid int, productname varchar(30), productqty int) -- Load Sample Data INSERT INTO @OrderDetail VALUES (1, 'Gift Card', 2) INSERT INTO @OrderDetail VALUES (1, 'Shipping', 1) INSERT INTO @OrderDetail VALUES (2, 'Gift Card', 2) INSERT INTO @OrderDetail VALUES (2, 'T-Shirt', 2) INSERT INTO @OrderDetail VALUES (2, 'Shipping', 2) Return to Top SQL Server 2005 / SQL Server 2008 Solution SQL 2000 Solutions.
SQL-Server Blog of Ritesh Shah --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. SQL 2005 Dynamic Pivot Query By Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right.
Let's have a quick look at the PIVOT function... SELECT <display_column_list> (SELECT <source_columns> as Column_Source ,<column_to_be_aggregated> as Column_Value ,<column_with_new_column_names> as Column_List FROM <datasource> ) as DataSource. 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 @PivotCol varchar(100), 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. You select your data, bang the button and with a bit of dragging and dropping, there it is. What they want to see is something like this (using NorthWind so those stuck with SQL 2000 can join in) No. Crosstabs and Pivot tables. 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 declare @sql varchar(8000) Select @sql = '' Select @OtherCols= isNull(', ' + @OtherCols,'') create table #pivot_columns (pivot_column_name varchar(100)) Select @sql='select ''' + replace( + @PivotCol,',',''' as pivot_column_name union all select ''')+'''' insert into #pivot_columnsexec(@sql) select @sql='' create table #pivot_columns_data (pivot_column_name varchar(100),pivot_column_data varchar(100)) Select @PivotCol='' Select @PivotCol=min(pivot_column_name) from #pivot_columns set nocount offset ansi_warnings on.
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. @sqlFromWhere NVARCHAR(512) = '', 024. 029. 035. 040. 048. 053. 057. 063. 065. 069. 071. 075. 077. 081. 083. 088. 089. 090. 091. 092. 093. 094. 095. 096. 097. 098. 103. 104. 105. 106. 107. 108. 109. 110. 111. 112. 113. 114. 115. 117. 119. -- * The real work starts here 121. -- Store the input dataset 122. 124. -- Create and populate the temporary table in one step 125. 126. 127. 131. -- Create the temporary table first (the only method that can be used if the data is coming from a stored proc) 132. 133. 134. 135. -- Populate it 136. 137. 138. 140. 142. -- Does the query we want to cross-tab already have a column called 'crossTabRowID'?
143. 145. 149. -- Add an identity column (we need a 'row ID') 150. 152. -- Generate the column list 153. 154. 155. 156. 159. 161. 165. 167. 168. 169. 172. 174. -- Build the results table; one row per group 175. 176. 177. 178. 181. 182. 184. 189.