'）'附近的语法不正确。 [英] Incorrect syntax near ')'.

问题描述

- 亲爱的参考资料有关于聚合功能的咨询，实际上PIVOT看：

--Dear good reference have a consultation with relation to the function of aggregation, in reality PIVOT look:

SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName],
(ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
FROM OINM M
INNER JOIN OITW T0 ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
WHERE M.DocDate <= getdate()

GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName
ORDER BY T0.[WhsCode], T0.[ItemCode]

- 我想转换PIVOT，这样做

--I want to convert the PIVOT , doing this

select WhsCode, [100] Sh,[101] TI, [102] Bod, [103], [104] PR, [199] CO from (
SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName]
--(ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) AS [Stock]
FROM OINM M
INNER JOIN OITW T0 ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OWHS T2 ON T0.WhsCode = T2.WhsCode
WHERE M.DocDate <= getdate())
V PIVOT  ( (ISNULL(SUM(M.InQty)-SUM(M.OutQty),0)) FOR WhsCode )
--GROUP BY T0.WhsCode, T2.WhsName, T0.ItemCode, T1.ItemName
--ORDER BY T0.[WhsCode], T0.[ItemCode]

- 但是在fu中发送错误聚合：

--but sends error in the function of aggregation :

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '('.

推荐答案

在PIVOT中，语法应如下所示：

In PIVOT the syntax should be like this:

PIVOT( AGGREGATE_FUNCTION(COLUMN) FOR HEADER_COLUMN)

而不是

and not

PIVOT( some-computation FOR HEADER_COLUMN)

你可以不要使用你想要的任何计算，但只使用聚合函数...

我不确定它是否可行，但你可以试试这个：

You can not use any computation you wish, but only aggregate functions...
I'm not sure that it will work, but you may try this:

PIVOT(SUM(ISNULL(M.InQty, 0)) - SUM(ISNULL(M.OutQty, 0)) FOR WhsCode)

或

or

PIVOT(SUM(ISNULL(M.InQty, 0) - ISNULL(M.OutQty, 0)) FOR WhsCode)

/ *版本1.1 * /

选择ItemCode，ItemName，ItmsGrpNam，sum（Sh）Sh，sum（TI）TI，sum（Bod）Bod，sum（ST）ST，sum（PR）PR，sum（CONG） CONG

起价

（

选择

ItemCode，ItemName，ItmsGrpNam，

isnull（[100]，0）Sh，

isnull（[101]，0）TI，

isnull（[102]，0）Bod，

isnull（[103]，0）ST，

isnull（[104]，0）PR，

isnull（[199]，0）CONG

来自（

SELECT T0。[WhsCode]，T2。[WhsName]，T0。[ItemCode]，T1。[ItemName]，T.ItmsGrpNam，sum（isnull（ M.InQty，0）-isnull（M.OutQty，0））[Saldo]

来自OINM M with（nolock）

INNER JOIN OITW T0（nolock）ON M.ItemCode = T0.ItemCode AND M.Warehouse = T0.WhsCode

INNER JOIN OITM T1（nolock）ON T0.ItemCode = T1.ItemCode

INNER JOIN OITB T （nolock）ON T1.ItmsGrpCod = T.ItmsGrpCod

INNER JOIN OWHS T2（noloc k）ON T0.WhsCode = T2.WhsCode

WHERE M.DocDate< = getdate（） - AND M.ItemCode ='0010-30-12159'

分组由T0。[WhsCode]，T2。[WhsName]，T0。[ItemCode]，T1。[ItemName]，T.ItmsGrpNam

）V

pivot

（

sum（V.Saldo）

for WhsCode in（[100]，[101]，[102]，[103]，[ 104]，[199]）

）作为Pvt

）按ItemCode，ItemName，ItmsGrpNam

/*Version 1.1 */
select ItemCode,ItemName,ItmsGrpNam,sum(Sh)Sh,sum(TI)TI,sum(Bod)Bod,sum(ST)ST,sum(PR)PR,sum(CONG)CONG
from
(
select
ItemCode,ItemName,ItmsGrpNam,
isnull([100],0) Sh,
isnull([101],0) TI,
isnull([102],0) Bod,
isnull([103],0) ST,
isnull([104],0) PR,
isnull([199],0) CONG
from (
SELECT T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName],T.ItmsGrpNam,sum(isnull(M.InQty,0)-isnull(M.OutQty,0))[Saldo]
FROM OINM M with (nolock)
INNER JOIN OITW T0 (nolock) ON M.ItemCode=T0.ItemCode AND M.Warehouse=T0.WhsCode
INNER JOIN OITM T1 (nolock) ON T0.ItemCode = T1.ItemCode
INNER JOIN OITB T (nolock) ON T1.ItmsGrpCod = T.ItmsGrpCod
INNER JOIN OWHS T2 (nolock) ON T0.WhsCode = T2.WhsCode
WHERE M.DocDate <= getdate()--AND M.ItemCode = '0010-30-12159'
group by T0.[WhsCode], T2.[WhsName], T0.[ItemCode], T1.[ItemName],T.ItmsGrpNam
) V
pivot
(
sum(V.Saldo)
for WhsCode in ([100],[101],[102],[103],[104],[199])
) as Pvt
) A
group by ItemCode,ItemName,ItmsGrpNam

组>

这篇关于'）'附近的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！