我想写一张SAP B1(t-sql)表,按月和月列出所有收入和支出项目。
我已成功用普万塔语写了一张书状,但我不想像Jan-11, Feb-11, Mar-11, 12-11。
相反,我希望各栏标题的定型生成,这样,如果我投入的话:
-------------------------------------- Query - Selection Criteria -------------------------------------- Posting Date greater or equal 01.09.10 Posting Date smaller or equal 31.08.11 [OK] [Cancel]
彩票将产生以下各栏:
Sep-10, Oct-10, Nov-10, .... Aug-11.
I guess DYNAMIC PIVOT can do the trick.
So, I modified one SQL obtained from another forum to suit my purpose, but it does not work. The error message I get is Incorrect Syntax near 20100901.
谁能帮助我找到我的错误?
注:在SAP B1中,[%1]是一个投入变量
我在此问:
/*Section 1*/
DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
-------------------------------------
/*Section 2*/
SELECT @listCol =
STUFF(
( SELECT DISTINCT ],[ + CONVERT(VARCHAR, MONTH(T0.RefDate), 102)
FROM JDT1
FOR XML PATH( ))
, 1, 2, ) + ]
------------------------------------
/*Section 3*/
SET @query =
SELECT * FROM
(
SELECT
T0.Account,
T1.GroupMask,
T1.AcctName,
MONTH(T0.RefDate) as [Month],
(T0.Debit - T0.Credit) as [Amount]
FROM dbo.JDT1 T0
JOIN dbo.OACT T1 ON T0.Account = T1.AcctCode
WHERE
T1.GroupMask IN (4,5,6,7) AND
T0.[Refdate] >= [%1] AND
T0.[Refdate] <= [%2]
) S
PIVOT
(
Sum(Amount)
FOR [Month] IN ( +@listCol+ )
) AS pvt
--------------------------------------------
/*Section 4*/
EXECUTE (@query)