关于枢纽奎里 [英] regarding pivot querry

问题描述

我想传递参数代替发票日期，但是我遇到错误，任何1个plz都可以帮助其紧急处理

i want to pass a parameter in place of invoice date but i m getting error can any 1 plz help its urgent

declare  @pivotcolumn nvarchar(500)
set @pivotcolumn= 'invoicedate'
begin
-- if  exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DEM1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
IF EXISTS(select * FROM sys.views where name = 'DEM1')

begin
drop view DEM1
end
alter table   tbTempSaleReport1
alter column qty_pcs int

--update  tblTempSalpurTaxregister set  fnAmount=fanSerialCode where  Client = 'Bill Amount'
declare @columns nvarchar(max)
select @columns=
         stuff(
              (  (select distinct
                ',['+invoicedate+']'
               from tbTempSaleReport1 where invoicedate <> ''
               For XML PATH(''))
               ),1,1,''
           )
exec('CREATE VIEW DEM1 AS
(select * from(select distinct(itemname) as itemname,sizename,casesize,accountname,qty_pcs,'+@pivotcolumn+' from tbTempSaleReport1 where '+@pivotcolumn+' <> '''')abc
pivot(sum(qty_pcs) for '+@pivotcolumn+' in( '+@columns+' )) pvt )'
)
end

推荐答案

尝试声明并设置另一个变量:

Try to declare and set another variable:

DECLARE @sSQL VARCHAR(MAX)

SET @sSQL = 'CREATE VIEW DEM1 AS
(SELECT * FROM (
    SELECT DISTINCT itemname,sizename,casesize,accountname,qty_pcs,'+@pivotcolumn+'
    FROM tbTempSaleReport1 where ' + @pivotcolumn + ' <> '''') abc
PIVOT(sum(qty_pcs) FOR ' + @pivotcolumn + ' IN( ' + @columns + ' )) pvt )'

在执行上面的代码之前，请查看变量的内容/值(消息):

Before you execute the code above, see the content/value of variable (Messages):

PRINT @sSQL
--EXEC(@sSQL)

这篇关于关于枢纽奎里的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！