关于枢纽奎里 [英] 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屋!
查看全文