sql 交叉表查询

最新推荐文章于 2022-09-21 21:35:27 发布

转载最新推荐文章于 2022-09-21 21:35:27 发布 · 2.7k 阅读

数据库类专栏收录该内容

32 篇文章

订阅专栏

本文介绍了一个使用T-SQL动态创建临时表并填充不同价格类型的示例过程。该过程通过游标遍历特定部门的所有价格类型，并为每种类型计算总和，最终将汇总的数据与产品信息关联展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Rpt_ProductPrice]
@IDEPARTID VARCHAR(20)
AS
DECLARE @myPriceType int
DECLARE aa CURSOR FOR
SELECT distinct IPRICETYPEID from tb_productprice where iDepartID=@IDEPARTID order by IPRICETYPEID
declare @selstr varchar(4000)

set @selstr=''
create table #tempProductprice
(
iProductID int
)

declare @vcRriceType varchar(20)
OPEN aa
while(1=1)
begin
FETCH NEXT FROM aa INTO @myPriceType
if @@FETCH_STATUS <> 0 break
set @vcRriceType = convert(nvarchar(20),@myPriceType)
set @selstr=@selstr+'sum(isnull(col'+@vcRriceType+',0)) as col'+@vcRriceType+','
execute ('ALTER TABLE #tempProductprice ADD col'+ @vcRriceType + ' decimal(9,2) default 0')
execute ('insert into #tempProductprice(iProductID,col'+@vcRriceType+') select iProductID,numPrice from tb_productprice where iDepartID = '+ @IDEPARTID+' and iPriceTypeID ='+@myPriceType)
end
set @selstr=substring(@selstr,1,len(@selstr)-1)

--exec ('select rowname ,' +@selstr +' from #tempProductprice group by rowname ')
set @selstr = 'select t1.*,t2.* from tb_Productinfo t1,(select iProductID ,' +@selstr +' from #tempProductprice group by iProductID) t2 '+
' where t1.iProductID = t2.iProductID '
exec (@selstr)

close aa
DEALLOCATE aa
drop table #tempProductprice