asked on

SQL - Dynamic pivot

Hi experts, here's a good one!

Say I have a table like this

Date Item Value
20090901 Prod1 100
20090902 Prod1 200
20090902 Prod2 100
20091005 Prod1 400
20091008 Prod1 500
20091105 Prod3 200
20091108 Prod1 200
20091205 Prod2 100

Etc, basically this is a transaction table with dates, items, and values.

I need to produce a dataset, with dynamic columns. I know my starting point is the month 200909. The result needs to look like this.

Item 200909 200910 200911 200912
Item1 300 400 500 800
Item2 200 500 600 1000
Item3 100 200 400 600

Etc. The values must be cumulative per item (they are not exact here, but you get the picture), and when we are into next month, ie 201001, this needs to be my far right colum, when we are in Feb it needs to be 201002 etc, each month a new column needs to be appended to the dataset, so some dynamic sql is needed, with a pivot too?

Can anyone help with this?

Roger

Reza Rad

If you need dynamic columns on your pivot script, you can create sql statement dynamically,
I mean create sql pivot select statement as a string , then execute this string like execute( @sql).
does it make sense?

Rogero

ASKER

I know that is what's required but I am looking for help on how to do it!

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John (Yiannis) Toutountzoglou

Try store procedure like this

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 

EXEC CrossTab
'SELECT Date,Item,Value From MyTable',
'Year(Date)',
'SUM(Value)[]',
'Item'

Open in new window

Rogero

ASKER

Thankyou so much Sharath, that has worked a treat :-)