August 2008 - Posts

Dynamic PIVOT in SQL Server 2005

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results
Consider this example

select * from
(
    select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
    INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID)
) as t
pivot
(
   
Count(OrderDate) for pivot_col in ([1996],[1997])
)
as p

which shows total orders of each employees for years 1996 and 1997

What if we want to have this for all the years available in the table
You need to use dynamic sql


This procedure is used to generate Dynamic Pivot results
The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post which can be used in SQL Server 2000

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries
varchar(100)
)
as

declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
 

create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
    select * from 
    (
        '
+@select+'
    ) as t
    pivot 
    (
        '
+@Summaries+' for pivot_col in ('+@pivot+')
    ) as p
'

exec(@sql)


 Purpose : Find total sales made by each employee for each year(from Employees and Orders table from Northwind databases)

Usage :

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) '
,
'Year(OrderDate)',
'Count(OrderDate)'

Purpose : Find total sales made by each company for each product(from products, order details and suppliers table from Northwind database)

Usage :

EXEC dynamic_pivot
'SELECT s.companyname,coalesce(od.unitprice*od.quantity ,0) as total_cost FROM northwind..products as p
inner join northwind..[order details] as od on p.productid=od.productid
inner join northwind..suppliers as s on p.supplierid=s.supplierid'
,
'productname',
'sum(total_cost)'

Common migration issues (version 2000 to 2005)

Many users complain that some new features are not working in SQL Server 2005 when the server is migrated from SQL Server 2000

These are the common tasks that should be done to avoid some migration issues

1 Change the compatibility level to 90
 
  
EXEC sp_dbcmptlevel 'your_db_name',90

  This will solve the errors that occur during the execution of the code that contains newly introduced functions 
  like row_number(), rank(),etc

2 Enable Ad Hoc Distributed Queries

  EXEC SP_CONFIGURE 'show advanced options', 1
  RECONFIGURE
  go
  EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
  RECONFIGURE
  go
  EXEC SP_CONFIGURE 'show advanced options', 0
  RECONFIGURE

  This will solve the errors that occur when you use OPENROWSET and OPENDATASOURCE commands

3 Install Service Pack2 for SQL Server 2005
 
  This will solve the errors that occur when you use Import/Export wizard

 

Posted by Madhivanan with 4 comment(s)
Filed under: , ,

Another use of GO command in SQL Server 2005

As you all know, GO command signals the end of the batch of T-SQL statements

However in SQL Server 2005, it is also used to execute set of commands for a specified number of times

Consider that you want to create a table that should have hundred random integer values. You can the methods like the ones specified in http://sqlblogcasts.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx or a while loop. You can also use GO command by specifying how many times it should run

create table #numbers(random_value int)
GO -- Signals the end of the batch

insert into #numbers(random_value) Select cast(100000*rand() as int)
GO 100 -- Signals that the above statement should run for 100 times

select random_value from #numbers

Now the table would have 100 random interger values

Here is Fun With Go

Posted by Madhivanan with 2 comment(s)
Filed under: , ,