带有MAX的SQL数据透视表
问题描述:
我对此感到困惑。我开发了一个SQL脚本TU获得这些信息: -带有MAX的SQL数据透视表
这里是代码: -
SELECT [COST UNIT],[GL ACCOUNT]+ ' ' + [GL], SUM([CLOSING BALANCE]) AS [TOTAL GL CLOSING BALANCE] FROM
(
SELECT AB.StartDate AS [DATE], AB.FreeTextField_04 AS [COST UNIT], IT.GLAccountDistribution AS [GL ACCOUNT], GL.Description AS [GL], ST.ItemCode AS [ITEM CODE] ,
ST.Description AS [DESCRIPTION],
IT.UserField_03 AS PACKAGING, ST.StockOnHandUOM AS [U/MEA.], IT.CostPriceStandard AS [STD COST PRICE], ST.PhyStkTakeQty AS [STOCK QTY],
CAST(ROUND((ST.PhyStkTakeQty * IT.CostPriceStandard),2) AS DECIMAL(12,2)) AS [CLOSING BALANCE]
FROM [001].DBO.CS738281StkTake ST INNER JOIN [001].DBO.Absences AB ON ST.ReqID = AB.ID
INNER JOIN [200].DBO.Items IT ON ST.ItemCode = IT.ItemCode
INNER JOIN [200].DBO.GRV_GLAccounts GL ON IT.GLAccountDistribution = GL.GLAccount
WHERE AB.StartDate = '2017-08-31 0:00:00.000'
--AND AB.FreeTextField_02 in ('1CTY','bsp')
AND IT.GLAccountDistribution IN (12010010,12010020,12010030,12010040,12010041,12010042,12010043,12010050,12010060,12010080,12030010)
--ORDER BY [GL ACCOUNT],[ITEM CODE]
) tmp
GROUP BY [GL ACCOUNT], [GL], [COST UNIT]
ORDER BY [COST UNIT],[GL ACCOUNT]
我的目标是做透视表看起来像这样: - Required Result
我尝试了很多次,但无法工作。谁能帮忙?非常感谢你:)
答
使用PIVOT
表操作员MAX
这样的:
WITH CTE
AS
(
SELECT [COST UNIT],[GL ACCOUNT]+ ' ' + [GL], SUM([CLOSING BALANCE]) AS [TOTAL GL CLOSING BALANCE] FROM
(
SELECT AB.StartDate AS [DATE], AB.FreeTextField_04 AS [COST UNIT], IT.GLAccountDistribution AS [GL ACCOUNT], GL.Description AS [GL], ST.ItemCode AS [ITEM CODE] ,
ST.Description AS [DESCRIPTION],
IT.UserField_03 AS PACKAGING, ST.StockOnHandUOM AS [U/MEA.], IT.CostPriceStandard AS [STD COST PRICE], ST.PhyStkTakeQty AS [STOCK QTY],
CAST(ROUND((ST.PhyStkTakeQty * IT.CostPriceStandard),2) AS DECIMAL(12,2)) AS [CLOSING BALANCE]
FROM [001].DBO.CS738281StkTake ST INNER JOIN [001].DBO.Absences AB ON ST.ReqID = AB.ID
INNER JOIN [200].DBO.Items IT ON ST.ItemCode = IT.ItemCode
INNER JOIN [200].DBO.GRV_GLAccounts GL ON IT.GLAccountDistribution = GL.GLAccount
WHERE AB.StartDate = '2017-08-31 0:00:00.000'
--AND AB.FreeTextField_02 in ('1CTY','bsp')
AND IT.GLAccountDistribution IN (12010010,12010020,12010030,12010040,12010041,12010042,12010043,12010050,12010060,12010080,12030010)
--ORDER BY [GL ACCOUNT],[ITEM CODE]
) tmp
GROUP BY [GL ACCOUNT], [GL], [COST UNIT]
)
SELECT *
FROM
(
SELECT GL, [COST UNIT], [TOTAL GL CLOSING BALANCE]
FROM CTE
) AS t
PIVOT
(
MAX([TOTAL GL CLOSING BALANCE])
FOR [COST UNIT] IN([1CTY], [AMCP], [AMPO])
) AS p
嗨迪米特里,感谢您的反馈意见。我试过了你的脚本,它返回一个错误: - Msg 8155,Level 16,State 2,Line 1 'MyCTE'的列2没有指定列名。 –