I'm trying to get the below query to work but struggling to get the PIVOT function to work properly, generally with syntax errors or multi-bound errors depending on how I structure it. I'm totally new to the PIVOT operator so I'm not sure how to fix this yet. Right now the month/period pulls vertically like the below sample data and I'm trying to get this period/month field (fpr) as the horizontal column.

SELECT t.com,
       t.fy,
       t.ledg,
       b.pl,
       b.desc,
       ISNULL(SUM(t.Debit_$ - t.Credit_$),0) AS Total_$
FROM (SELECT  com,
              fy,
              fpr,
              ledg,
              CASE WHEN dc = 1 THEN amt_1 ELSE 0 END AS Debit_$,
              CASE WHEN dc = 2 THEN amt_1 ELSE 0 END AS Credit_$
      FROM dbo.mas31) AS t
     LEFT JOIN dbo.sam13 AS b ON t.ledg = b.ledg
                             AND t.com = b.com)
PIVOT (SUM(Total_$)
       FOR fpr IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pt

CodePudding user response：

Do the calculation for the total in the source query.
And only select the fields in the source query that will be aggregated on by the pivot.

(untested notepad scribble)

SELECT * 
FROM (
  SELECT m.com, m.fy, m.ledg, s.pl, s.[desc], m.fpr
  -- debet - credit = total
  , (IIF(m.dc=1, m.amt_1, 0) - IIF(m.dc=2, m.amt_1, 0)) AS [Total_$]
  FROM dbo.mas31 AS m
  LEFT JOIN dbo.sam13 AS s 
    ON s.ledg = m.ledg
   AND s.com = m.com
) AS Src
PIVOT (
  SUM([Total_$])
  FOR fpr IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS Pvt

CodePudding user response：

Like one of the comments mention, conditional aggregation is pretty simple to read/manage and more flexible if you need to aggregate multiple columns, instead of just grouping by them like pivot would.

select 
    com, 
    led, 
    sum(case when fpr=1 then total end) as [1], 
    sum(case when fpr=2 then total end) as [2]
from (...) t
group by 
    com,
    led