select j_ItemCode, j_dianhao ,zongchang=
sum (case when brand not in ('kk) then chang*zanbi/100 else 0 end),
a正常货架长度=sum(case when brand IN('y') then (chang*zanbi/100) else 0 end),
b正常货架长度=sum(case when brand IN('z') then (chang*zanbi/100) else 0 end),
c正常货架长度=sum(case when brand IN('m') then (chang*zanbi/100) else 0 end)
into #wt from chenghui_huojia_changhui_brand group by j_dianhao,j_ItemCode
--上面是对的
select j_ItemCode, j_dianhao ,zongchang,[Course] ,[Score]
from
#wt
unpivot
([Score] for [Course] in([a正常货架长度],[b正常货架长度],
[c正常货架长度],))b
--错误消息
消息 102,级别 15,状态 1,第 5 行
'Score' 附近有语法错误。
sum (case when brand not in ('kk) then chang*zanbi/100 else 0 end),
a正常货架长度=sum(case when brand IN('y') then (chang*zanbi/100) else 0 end),
b正常货架长度=sum(case when brand IN('z') then (chang*zanbi/100) else 0 end),
c正常货架长度=sum(case when brand IN('m') then (chang*zanbi/100) else 0 end)
into #wt from chenghui_huojia_changhui_brand group by j_dianhao,j_ItemCode
--上面是对的
select j_ItemCode, j_dianhao ,zongchang,[Course] ,[Score]
from
#wt
unpivot
([Score] for [Course] in([a正常货架长度],[b正常货架长度],
[c正常货架长度],))b
--错误消息
消息 102,级别 15,状态 1,第 5 行
'Score' 附近有语法错误。
([Score] for [Course] in([a正常货架长度],[b正常货架长度],
[c正常货架长度],))b
多个分号
你参考下列子:
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--select * from PVT
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM PVT
UNPIVOT (
Orders FOR Employee IN ([Emp1], [Emp2], [Emp3], [Emp4], [Emp5])
)AS unpvt
GO