狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

date=業務日期；Item=商品名稱；saleqty=銷售數量；

-- 建立測試數據（表）
create table test (Date varchar(10), item char(10),saleqty int)
insert test values(''2010-01-01'',''AAA'',8)
insert test values(''2010-01-02'',''AAA'',4)
insert test values(''2010-01-03'',''AAA'',5)
insert test values(''2010-01-01'',''BBB'',1)
insert test values(''2010-01-02'',''CCC'',2)
insert test values(''2010-01-03'',''DDD'',6)

需要實現的報表樣式：每一行既每一天，顯示所有商品（列）該天的銷售數量；

實現的方法和思路如下：

-- 實現結果的靜態SQL語句寫法
-- 整理報表需要的格式
select date,
case item when ''AAA'' then saleqty when null then 0 end as AAA,
case item when ''BBB'' then saleqty when null then 0 end as BBB,
case item when ''CCC'' then saleqty when null then 0 end as CCC,
case item when ''DDD'' then saleqty when null then 0 end as DDD
from test

-- 按日期匯總行
select date,
sum(case item when ''AAA'' then saleqty when null then 0 end) as AAA,
sum(case item when ''BBB'' then saleqty when null then 0 end) as BBB,
sum(case item when ''CCC'' then saleqty when null then 0 end) as CCC,
sum(case item when ''DDD'' then saleqty when null then 0 end) as DDD
from test 
group by date

-- 處理數據：將空值的欄位填入數字0；
select date,
isnull (sum(case item when ''AAA'' then saleqty end),0) as AAA,
isnull (sum(case item when ''BBB'' then saleqty end),0) as BBB,
isnull (sum(case item when ''CCC'' then saleqty end),0) as CCC,
isnull (sum(case item when ''DDD'' then saleqty end),0) as DDD
from test 
group by date

靜態SQL語句編寫完成！

-- 需要動態實現的SQL部分
isnull (sum(case item when ''AAA'' then saleqty end),0) as AAA,
isnull (sum(case item when ''BBB'' then saleqty end),0) as BBB,
isnull (sum(case item when ''CCC'' then saleqty end),0) as CCC,
isnull (sum(case item when ''DDD'' then saleqty end),0) as DDD

-- 動態語句的實現
select ''isnull (sum(case item when ''''''+item+'''''' then saleqty end),0) as [''+item+'']'' 
from (select distinct item from test) as a
-- 這一步很關鍵：利用結果集給變量賦值；

-- 完成！
declare @sql varchar(8000)
set @sql = ''select Date''
select @sql = @sql + '',isnull (sum(case item when ''''''+item+'''''' then saleqty end),0) as [''+item+'']'' 
from (select distinct item from test) as a
select @sql = @sql+'' from test group by date''
exec(@sql)

-- 刪除測試數據（表）
drop table test