[_items](
[itemID] [int] IDENTITY(1,1) NOT NULL,
[ItemDesc] [nvarchar](50)
)

[_Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [int] NULL,
[Qty] [int] NULL,
[SalesDate] [datetime] NULL
)

select _Items.itemID, _Items.ItemDesc, yr2008.yr2008, yr2009.yr2009, yr2010.yr2010, yr2011.yr2011  from _items 
left outer join 
(select itemID, sum(Qty) as yr2008 from _sales where (year(SalesDate) = 2008)
group by itemID, year(SalesDate)) as yr2008 on _items.itemID = yr2008.itemID
left outer join 
(select itemID, sum(Qty) as yr2009 from _sales where (year(SalesDate) = 2009)
group by itemID, year(SalesDate)) as yr2009 on _items.itemID = yr2009.itemID
left outer join 
(select itemID, sum(Qty) as yr2010 from _sales where (year(SalesDate) = 2010)
group by itemID, year(SalesDate)) as yr2010 on _items.itemID = yr2010.itemID
left outer join 
(select itemID, sum(Qty) as yr2011 from _sales where (year(SalesDate) = 2011)
group by itemID, year(SalesDate)) as yr2011 on _items.itemID = yr2011.itemID

exec dynamic_pivot (
'SELECT ItemID, Qty, SalesDate From _Sales',
'year(SalesDate)',
'SUM(Qty)'
)

exec dynamic_pivot
     @select    = 'SELECT ItemID, Qty, SalesDate From _Sales',
     @PivotCol  = 'year(SalesDate)',
     @Summaries = 'SUM(Qty)'

exec dynamic_pivot
     @select    = 'SELECT _items.itemID, _items.ItemDesc, _Sales.Qty FROM _items LEFT OUTER JOIN _Sales ON _items.itemID = _Sales.ItemID',
     @PivotCol  = 'year(SalesDate)',
     @Summaries = 'SUM(Qty)'

exec dynamic_pivot
     'SELECT ItemID, Qty, SalesDate From _Sales',
     'year(SalesDate)',
     'SUM(Qty)'

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

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[dynamic_pivot]
(
@select varchar(2000),
@PivotCol varchar(100), 
@Summaries varchar(100),
@OrderBy varchar(2),
@AscDesc varchar(4)
) 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
	order by '+@OrderBy+' '+@AscDesc+'
' 

exec(@sql) 

ALTER procedure [dbo].[dynamic_pivot]
(
@select varchar(2000),
@PivotCol varchar(100), 
@Summaries varchar(100),
@OrderBy varchar(100),
@AscDesc varchar(4)
) as 

    ) as p
'
	+ isnull('ORDER BY '+ @OrderBy, '')

exec dynamic_pivot
    @select = 'SELECT _items.itemID, _items.ItemDesc, _Sales.Qty FROM _items JOIN _Sales ON _items.itemID = _Sales.ItemID',
    @PivotCol = 'year(SalesDate)',
    @Summaries = 'SUM(Qty)'
    @OrderBy = 'itemID DESC, ItemDesc'

ADODB.Recordset error '800a0e78' 
Operation is not allowed when the object is closed. 
/ReportPivot.asp, line 116

sqlstr = "EXEC dynamic_pivot......."
set recobj = connection_open(sqlstr)
strRecordCount = recobj.RecordCount '<-- Line 116

'connection_open(sqlstr) as below
function connection_open(sqlstring)
  dim recobj01, connobj
  set connobj = Server.CreateObject("ADODB.Connection")
  connobj.Open "DSN=myDSN","userName","Password"
  set recobj01 = Server.CreateObject("ADODB.Recordset")
  recobj01.CursorLocation = 3
  recobj01.Open sqlstring, connobj
  set connection_open = recobj01
  set recobj01 = nothing
  set connobj = nothing
  if err <> 0 then
    response.write sqlstr
    response.write "<DIV CLASS=""warning"">An error occurred: " & err.description & "</DIV>"
  end if
end function

CREATE TABLE _TEST (SalesPerson CHAR(1),SalesVol INT, SalesDate SMALLDATETIME)
INSERT INTO _TEST VALUES ('X',0,'2000-01-01')
INSERT INTO _TEST VALUES ('Y',10,'2000-01-01')
INSERT INTO _TEST VALUES ('Z',0,'2000-01-01')
INSERT INTO _TEST VALUES ('X',0,'2000-01-02')
INSERT INTO _TEST VALUES ('Y',20,'2000-01-02')
INSERT INTO _TEST VALUES ('Z',10,'2000-01-02')
INSERT INTO _TEST VALUES ('X',10,'2000-01-03')
INSERT INTO _TEST VALUES ('Y',0,'2000-01-03')
INSERT INTO _TEST VALUES ('Z',20,'2000-01-03')

EXEC dynamic_pivot 
@select = 'SELECT SalesPerson, SalesVol, SalesDate FROM _TEST', 
@PivotCol = 'SalesPerson', 
@Summaries = 'sum(SalesVol)'

Msg 8624, Level 16, State 17, Line 2
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.