Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Dynamic PIVOT in SQL Server 2005

Aug 27 2008 1:37PM by Madhivanan   

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)'

Tags: t-sql, sql_server, dynamic_cross_tab, dynamic_pivot, cross_tab, pivot,


Madhivanan
3 · 40% · 12936
4
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

22  Comments  

  • Madhivanan, lots of nice reference material! I very often often google for "Madhivanan+dynamic pivoting" and copy the code from here.

    I noticed that you don't have a "drop table #pivot_columns" in the code. May not matter because the SQL OS should take care of it.

    commented on Apr 14 2011 10:01PM
    SunitaBeck
    680 · 0% · 49
  • Sunita, Thanks for the feedback. As the temporary table is created inside a procedure, it will be dropped automatically after the procedure is executed

    commented on Apr 15 2011 2:56AM
    Madhivanan
    3 · 40% · 12936
  • yes, and you should always clean up after yourself regardless

    commented on Mar 26 2012 12:54PM
    Brett
    3071 · 0% · 2
  • Hi

    I think I need to use pivot here I was wondering if you could help I need to compare a sum of multiple rows to the sum of a single row. Thanks for your help!!!

    I need to comapre the sum of tranamount where the transeq is > 0 to the remaining balance of the 0 transeq. this needs to be grouped by account and trannum

    I have sample data just cant get it to format correctly here

    commented on Oct 24 2012 4:56AM
    vmanfredi
    1114 · 0% · 23
  • Post sample data with expected result. Use Code sample option when posting

    commented on Oct 24 2012 8:17AM
    Madhivanan
    3 · 40% · 12936
  • Hi

    No matter what I do o cant get the code sample to format correct

    commented on Oct 25 2012 5:25AM
    vmanfredi
    1114 · 0% · 23
  • You need to also post the expected result

    commented on Oct 25 2012 5:44AM
    Madhivanan
    3 · 40% · 12936
  • Hi here is the data. I need to sum the tran amount and compare to the remain balance for the 0 tran_sequence record.

    Notice the account can change but tran num stays the same.
    
        print(Account  tran_num tran_sequence Tran_Amount Remain_Balance Status
    3gg001    P071009         0                -40274-16          0                    P
    3gg001    P071009         1                12879.20          Null                 P
    3gg001    P071009         2                27394.96          Null                 P
    3gg001    P071009         3                -27394.96         Null                 P
    3gg001    P071009         4                12879.20          Null                 P
    3gg001    P071009         5                12738              Null                  P
    3gg001    P071009         6                27536.16          Null                 P
    ALP002   P071009         0                 -77556.1           0                    P
    ALP002   P071009         1                77556.1           Null                 P
    RSR001  180986            0                200.00               10.00             P
    RSR001   180986            1               -190.00              0                   P);
    
    commented on Oct 25 2012 5:57AM
    vmanfredi
    1114 · 0% · 23
  • here is the result

    Account    Tran_Num  Tran_Amount Remain_Bal   Diff
    3gg001       P071009        -40274.16                0            0
    ALP002       P071009       -77556.10                0            0
    RSR001       180986         200.00             10.00      190.00
    
    commented on Oct 25 2012 6:03AM
    vmanfredi
    1114 · 0% · 23
  • Try this

    select 
        Account,tran_num,
        sum(case when tran_sequence=0 then Tran_amount else 0 end) as Tran_amount, 
        sum(Tran_amount) as differences, 
        sum(case when tran_sequence=0 then Tran_amount else 0 end)-sum(Tran_amount) as remaining_balance  
    from your_table 
    group by Account,tran_num
    
    commented on Oct 25 2012 10:01AM
    Madhivanan
    3 · 40% · 12936
  • Thanks When I ran it the results below came out for this group of tranactions . the sum of the tran_amount should come out to 0

        ACCOUNT TRAN_NUM	TRAN_SEQUENCE	TRAN_AMOUNT	REMAIN_BALANCE	STATUS	COMMENT
        SEV002  147773	0	36141.01	0	P	AOK519355
        SEV002  147773	1	321.05	NULL	P	WEIGHT
        SEV002  147773	2	-36462.06	NULL	P	Payment Applied
    
    returns this
    
    Account tran_num	Tran_amount	differences	remaining_balance
    SEV002  147773	36141.01	7.27595761418343E-12	36141.01
    
    commented on Oct 25 2012 10:36AM
    vmanfredi
    1114 · 0% · 23
  • I just noteced the tran amount is a Float data type

    commented on Oct 25 2012 10:44AM
    vmanfredi
    1114 · 0% · 23
  • where would i put the cast statement?

    commented on Oct 25 2012 10:51AM
    vmanfredi
    1114 · 0% · 23
  • Try this

    select 
        Account,tran_num,
        sum(case when tran_sequence=0 then Tran_amount else 0 end) as Tran_amount, 
        sum(remain_balance) as reamin_balance, 
        sum(case when tran_sequence=0 then Tran_amount else 0 end) +sum(case when tran_sequence<>0 then Tran_amount else 0 end)  as differences
        from your_table
    group by Account,tran_num
    
    commented on Oct 25 2012 10:57AM
    Madhivanan
    3 · 40% · 12936
  • That worked perfect...Thanks

    commented on Oct 25 2012 12:56PM
    vmanfredi
    1114 · 0% · 23
  • What if i need to use a stored procedure as "select from..." whith arguments? Thanks form help

    commented on Feb 14 2013 9:33AM
    albertsmus
    2765 · 0% · 4
  • albertsmus, see if this helps http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx

    commented on Feb 14 2013 11:41PM
    Madhivanan
    3 · 40% · 12936
  • Ok i made it thanks. Is it possible to add summaries below?

    commented on Feb 15 2013 3:36AM
    albertsmus
    2765 · 0% · 4
  • Did you mean total for each pivot columns?

    commented on Feb 15 2013 4:12AM
    Madhivanan
    3 · 40% · 12936
  • Yes.

    commented on Feb 15 2013 6:04AM
    albertsmus
    2765 · 0% · 4
Previous 1 | 2 Next

Your Comment


Sign Up or Login to post a comment.

"Dynamic PIVOT in SQL Server 2005" rated 5 out of 5 by 4 readers
Dynamic PIVOT in SQL Server 2005 , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]