SQL 数据透视表小计语法错误 [英] SQL Pivot Table Subtotal Syntax error

问题描述

希望有人能帮助我.我能够将这个 SQL 脚本放在一起，但它在这里和那里都出现了小错误.我一直在尝试调试一个多星期了.请帮忙.第一条错误消息是')' 附近的语法不正确."如果你修复它会继续抛出更多，所以我想我没有正确编码.我无法保存有效的更改.它告诉我保存请求已中止.使用 SQL Server 2008 r2

Hoping someone can help me. I was able to put together this SQL script but it coming back with minor errors here and there. I'be been trying to debug for over a week now. Please help. the first error message is "Incorrect syntax near ')'." If you fixe that it keeps on throwing more out so I'm thinking I'm not coding this correctly. I am unable to save changes that do work. it tell me the save request was aborted. Working with SQL Server 2008 r2

SELECT     
PublicationID AS PubID, (PubNum + '-  ' + PubTitle) AS [Pub Descr], 
CONVERT(Varchar(10), [Datestamp], 101) AS [Date Printed], 
QtyPrinted AS [Qty Printed], 
[2] AS [Tyler Inventory], 
[1] AS [Central Inventory], 
[3] AS [Mailing House Inventory],

(
SELECT     SUM(S)
FROM  
(
SELECT [1] UNION ALL
SELECT [2] UNION ALL
SELECT [3]
) AS T (S)) AS [Current Inventory], 
RecycledQty AS [Recycled], 
MailingVendorName AS [Mailing Vendor], 
PrintVendorName AS [Print Vendor]

FROM
(
SELECT 
PublicationID, LocationID, Balance, PubNum, 
PubTitle, ItemPerCase, Datestamp, Deleted,     
RecycledQty, MailingVendorName, PrintVendorName, 
QtyPrinted

FROM          
(
dbo.view_PubInventory_Main_Summary_RAW) x PIVOT (sum(balance) FOR 
LocationID IN ([1],   [2], [3])) p)
    SELECT     *
     FROM  
     (SELECT   PUBID, [Pub Descr], [Date Printed], [Qty Printed], 
      [Tyler Inventory], [Central Inventory], 
      [Mailing House Inventory], [Current Inventory], [Recycled],
      [Mailing Vendor]
      FROM GG
      ) AS T

推荐答案

很难完全遵循您的需求，但我认为您希望 Current Inventory 只是 [1]+[2]+[3]，并且您没有为您的子查询设置别名.底部的查询看起来不错.

Hard to follow exactly what you're after, but I think you want Current Inventory to just be [1]+[2]+[3], and you didn't alias your subquery. The query at the bottom looks fine.

 SELECT PublicationID AS PubID
      , PubNum + '-  ' + PubTitle AS [Pub Descr]
      , CONVERT(VARCHAR(10), [Datestamp], 101) AS [Date Printed]
      , QtyPrinted AS [Qty Printed]
      , [2] AS [Tyler Inventory]
      , [1] AS [Central Inventory]
      , [3] AS [Mailing House Inventory]
      , [1]+[2]+[3] AS [Current Inventory]
      , RecycledQty AS [Recycled]
      , MailingVendorName AS [Mailing Vendor]
      , PrintVendorName AS [Print Vendor]
 FROM   ( SELECT    PublicationID
                  , LocationID
                  , Balance
                  , PubNum
                  , PubTitle
                  , ItemPerCase
                  , Datestamp
                  , Deleted
                  , RecycledQty
                  , MailingVendorName
                  , PrintVendorName
                  , QtyPrinted
           FROM     dbo.view_PubInventory_Main_Summary_RAW
           PIVOT ( SUM(balance) FOR LocationID IN ( [1], [2], [3] ) ) p
        )AS Sub

这篇关于SQL 数据透视表小计语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！