Beyondrelational

Tuesday, July 13, 2010

Dynamic PIVOT in SQL Server 2005

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)

Select A.AddNo,B.Subject,A.Marks
From tbl_MarkDetails AS A, tbl_SubCode AS B
Where A.SubjectCode=B.SubjectCode

EXEC dynamic_pivot
'Select A.AddNo,coalesce(A.Marks,0) as Marks
From tbl_MarkDetails AS A, tbl_SubCode AS B
Where A.SubjectCode=B.SubjectCode',
'Subject',
'SUM(Marks)'


Drop Table tbl_SubCode
Drop Table tbl_MarkDetails

Purpose : Find total sales made by each person for each year(from Persons and Orders table)

Usage :

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM Persons as e
INNER JOIN Orders as o ON (e.P_ID=o.P_ID) ',
'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)'

Dynamic Crosstab with multiple PIVOT Columns

CREATE procedure CrossTab 
(
@select varchar(2000),
@PivotCol varchar(100), 
@Summaries varchar(100), 
@GroupBy varchar(100),
@OtherCols varchar(100) = Null
)

AS
set nocount on
set 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_columns
exec(@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

While @PivotCol>''
Begin
    insert into #pivot_columns_data(pivot_column_name,pivot_column_data) 
    exec 
    (
    'select distinct ''' + @PivotCol +''' as pivot_column_name, convert(varchar(100),' + @PivotCol + ') as pivot_column_data    from 
    ('+
        @select
    +'
    ) T'
    )

    Select @PivotCol=min(pivot_column_name) from #pivot_columns where pivot_column_name>@PivotCol
end 
select 
    @sql = @sql + ', ' + 
    replace(
        replace(
                @Summaries,'(','(CASE WHEN ' + Pivot_Column_name + '=''' + 
                pivot_column_data + ''' THEN ' 
                    ),
            ')[', ' END) as [' + pivot_column_data 
                )
from #pivot_columns_data
order by pivot_column_name

exec 
(
     'select ' + @GroupBy +@OtherCols +@sql + 
    ' from (
    '+
        @select 
    +'
    ) T
    GROUP BY ' + @GroupBy
) 
drop table #pivot_columns
drop table #pivot_columns_data

set nocount off
set ansi_warnings on
Now execute the procedure by supplying two pivot columns City and Year(Orderdate)

EXEC CrossTab
'SELECT LastName, OrderDate,City FROM Persons 
INNER JOIN Orders ON (Persons.P_ID=Orders.P_ID) ',
'City,Year(OrderDate)',
'Count(LastName)[]',
'LastName'

Friday, July 9, 2010

Export all table data to Text file / Excel file



DECLARE @tables TABLE(table_name VARCHAR(100))

INSERT INTO @tables
SELECT name FROM sysobjects WHERE xtype ='u'



DECLARE @table_name VARCHAR(1000)

SELECT @table_name=MIN(table_name)FROM @tables

WHILE @table_name>''
BEGIN
        DECLARE @str VARCHAR(1000)  
        SET @str='EXEC Master..xp_Cmdshell ''bcp "SELECT * FROM '+db_name()+'..'+@table_name+'" queryout "C:\data\'+@table_name+'.xls" -Sservername -Uusername -Ppassword -T -c'',no_output'  
        EXEC(@str)  
SELECT @table_name=MIN(table_name)FROM @tables WHERE table_name>@table_name
        
-- DECLARE @str1 VARCHAR(1000)  
--      SET @str1='EXEC Master..xp_Cmdshell ''bcp "SELECT * FROM '+db_name()+'..'+@table_name+'" queryout "C:\data\'+@table_name+'.txt" -Sservername -Uusername -Ppassword -T -c'',no_output'  
--      EXEC(@str1)  
-- SELECT @table_name=MIN(table_name)FROM @tables WHERE table_name>@table_name
END 

Tuesday, July 6, 2010

Quickly Delete Blank Rows

Quickly Delete Blank Rows From A Long List (Excel 2003, Excel 2007)

  1. First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .
  2. Now, select the cells in one column from the top of your list to the bottom.
  3. Make sure that all the blank cells in this selected range are the rows you want to delete.
  4. Press the F5 key on your keyboard (or select Edit, Goto).
  5. Click the Special button.
  6. Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.
  7. Now choose Edit, Delete, select the Entire Row option and click OK.
delete blank rows in excel