title: SQL - Debugging dynamic PIVOT code with UNION ALL
body: <p>I am in need of help! The first part of my code is as follows:</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
  
  SET @cols= STUFF((SELECT ','+QUOTENAME(c.CURCODE) FROM (
  
  (
  
  SELECT DISTINCT b.CURCODE,a.TRCURR FROM LG_011_BNCREDITCARD a INNER JOIN L_CURRENCYLIST b 
  ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
  
  )
  
  UNION
  
  (
  
  SELECT DISTINCT b.CURCODE,a.TRCURR
  
  FROM LG_011_LEASINGPAYMENTSLNS a
  
  INNER JOIN LG_011_PURCHOFFER z
  ON a.LEASINGREF=z.LEASINGREF
  
  INNER JOIN
  (SELECT 
  MAX(LOGICALREF) LOGICALREF,
  LEASINGREF
  FROM LG_011_PURCHOFFER
  GROUP BY LEASINGREF) y
  ON z.LOGICALREF=y.LOGICALREF
  
  INNER JOIN L_CURRENCYLIST b
  ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
  
  WHERE z.STATUS=4
  
  )
  
  UNION
  
  (
  
  SELECT DISTINCT cur.CURCODE,cs.TRCURR FROM 
  
  LG_011_01_CSTRANS a
  
  INNER JOIN
  
  (
  SELECT 
  CSREF,
  MAX(STATNO) [STATNO]
  
  FROM LG_011_01_CSTRANS
  GROUP BY CSREF) b
  ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
  
  INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
  
  WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
  
  )
  
  UNION
  
  (
  
  SELECT DISTINCT cur.CURCODE, pt.TRCURR
  
  FROM LG_011_01_PAYTRANS pt
  INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
  
  WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU')
  
  )
  
  ) c ORDER BY c.TRCURR FOR XML PATH(''), TYPE 
  ).value('.','NVARCHAR(MAX)'),1,1,'')
  </code></pre>
  
  <hr>
  
  <p>Up to now, there aren't any problems. Using the code above,</p>
  
  <pre><code>SELECT @cols  
  </code></pre>
  
  <p>gives the output:</p>
  
  <pre><code>[TL],[USD],[EUR]
  </code></pre>
  
  <hr>
  
  <p>The second part of the query is where the problem occurs. The code is structured as follows:</p>
  
  <pre><code>SET @query ='
  
  SELECT
  
  col1 [TÜR],
  col2 [KOD],
  col3 [BANKA/CARİ],
  col4 [BANKA HESABI],
  col5 [AÇIKLAMA],
  col6 [VADE],
  ' + @cols + '
  
  FROM
  
  (
  Table 1
  
  UNION ALL
  
  Table 2
  
  UNION ALL
  
  Table 3
  
  UNION ALL
  
  Table 4
  
  ) x
  
  PIVOT
  (
  SUM(AMOUNT)
  FOR CURCODE IN ('+ @cols+ ')
  
  ) xx
  
  ORDER BY xx.col6,xx.TRCURR, xx.col1, xx.col3, xx.col4, xx.col2
  '
  
  execute(@query)
  </code></pre>
  
  <hr>
  
  <p>The second part does not work. I can make it work by doing three things:</p>
  
  <p>1- Making the pivot static (ie replacing <code>'+ @cols +'</code> with <code>[TL],[USD],[EUR]</code></p>
  
  <p>2- Removing Table 2, Table 3 or Table 4 from the UNION ALL combinations. Somehow, removing only one table makes the dynamic pivot work.</p>
  
  <p>3- I can remove one of Table 2, Table 3, or Table 4 and add one of the remaining tables again as another UNION ALL addition. Strangely, this works as well. So the problem might be that there might be one aspect of the 4 tables together that interferes with the PIVOT statement. </p>
  
  <p>But I would like to include all the tables from 1 to 4 and I would like to have a dynamic pivot that adjusts itself for changes!</p>
  
  <h2>Please note that if I run the SELECT statement without the PIVOT construct with all the UNION ALLs, the code works perfectly fine as well (As I have indicated below)</h2>
  
  <p>My original code is:</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
  
  SET @cols= STUFF((SELECT ','+QUOTENAME(c.CURCODE) FROM (
  
  (
  
  SELECT DISTINCT b.CURCODE,a.TRCURR FROM LG_011_BNCREDITCARD a INNER JOIN L_CURRENCYLIST b 
  ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
  
  )
  
  UNION
  
  (
  
  SELECT DISTINCT b.CURCODE,a.TRCURR
  
  FROM LG_011_LEASINGPAYMENTSLNS a
  
  INNER JOIN LG_011_PURCHOFFER z
  ON a.LEASINGREF=z.LEASINGREF
  
  INNER JOIN
  (SELECT 
  MAX(LOGICALREF) LOGICALREF,
  LEASINGREF
  FROM LG_011_PURCHOFFER
  GROUP BY LEASINGREF) y
  ON z.LOGICALREF=y.LOGICALREF
  
  INNER JOIN L_CURRENCYLIST b
  ON a.TRCURR=b.CURTYPE OR (a.TRCURR=0 AND b.CURTYPE=160)
  
  WHERE z.STATUS=4
  
  )
  
  UNION
  
  (
  
  SELECT DISTINCT cur.CURCODE,cs.TRCURR FROM 
  
  LG_011_01_CSTRANS a
  
  INNER JOIN
  
  (
  SELECT 
  CSREF,
  MAX(STATNO) [STATNO]
  
  FROM LG_011_01_CSTRANS
  GROUP BY CSREF) b
  ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
  
  INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
  
  WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
  
  )
  
  UNION
  
  (
  
  SELECT DISTINCT cur.CURCODE, pt.TRCURR
  
  FROM LG_011_01_PAYTRANS pt
  INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
  
  WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU')
  
  )
  
  ) c ORDER BY c.TRCURR FOR XML PATH(''), TYPE 
  ).value('.','NVARCHAR(MAX)'),1,1,'')
  
  
  SET @query ='
  
  SELECT
  
  col1 [TÜR],
  col2 [KOD],
  col3 [BANKA/CARİ],
  col4 [BANKA HESABI],
  col5 [AÇIKLAMA],
  col6 [VADE],
  '+ @cols +'
  
  FROM
  
  (
  
  (
  
  SELECT
  ''LEASİNG'' [col1],
   d.REGNR [col2],
   cl.DEFINITION_ [col3],
   '''' [col4],
   d.DESCRIPTION [col5],
   c.PAYMENTDATE [col6],
   a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT],
   c.TRCURR [TRCURR],
   e.CURCODE [CURCODE]
  
  
  FROM
  (SELECT
  LOGICALREF,
  SUM(PAYMENTTOTAL) AS KDVSIZ,
  SUM(INTTOTAL) AS FAIZ,
  SUM(MAINTOTAL) AS ANAPARA,
  SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
  SUM(VATINPAYMENTTOTAL) AS KDVLI
  
  FROM LG_011_LEASINGPAYMENTSLNS
  WHERE TRANSTYPE=0
  GROUP BY LOGICALREF) a
  
  LEFT OUTER JOIN
  
  (SELECT
  PARENTREF,
  SUM(PAYMENTTOTAL) AS KDVSIZ,
  SUM(INTTOTAL) AS FAIZ,
  SUM(MAINTOTAL) AS ANAPARA,
  SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
  SUM(VATINPAYMENTTOTAL) AS KDVLI
  FROM LG_011_LEASINGPAYMENTSLNS
  WHERE TRANSTYPE=1
  GROUP BY PARENTREF
  ) b 
  ON a.LOGICALREF= b.PARENTREF
  
  INNER JOIN
  LG_011_LEASINGPAYMENTSLNS c
  ON a.LOGICALREF=c.LOGICALREF
  
  
  INNER JOIN
  
  LG_011_LEASINGREG d
  ON c.LEASINGREF=d.LOGICALREF
  
  INNER JOIN
  LG_011_PURCHOFFER z
  ON c.LEASINGREF=z.LEASINGREF
  
  INNER JOIN
  (SELECT 
  MAX(LOGICALREF) LOGICALREF,
  LEASINGREF,
  CLIENTREF
  FROM LG_011_PURCHOFFER
  GROUP BY CLIENTREF,LEASINGREF) y
  ON z.LOGICALREF=y.LOGICALREF
  
  INNER JOIN LG_011_CLCARD cl
  ON z.CLIENTREF=cl.LOGICALREF
  
  INNER JOIN L_CURRENCYLIST e
  ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)
  
  
  WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)&lt;&gt;0
  
  )
  
  UNION ALL
  
  (
  
  SELECT
  ''ÇEK'',
  cs.NEWSERINO,
  bn.DEFINITION_,
  ban.DEFINITION_,
  cl.DEFINITION_,
  cs.DUEDATE,
  cs.AMOUNT,
  cs.TRCURR,
  cur.CURCODE
  
  FROM
  
  LG_011_01_CSTRANS a
  
  INNER JOIN
  
  (
  SELECT 
  CSREF,
  MAX(STATNO) [STATNO]
  
  FROM LG_011_01_CSTRANS
  GROUP BY CSREF) b
  ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
  
  INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
  INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
  INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
  INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF
  
  WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
  
  )
  
  UNION ALL
  
  (
  
  SELECT
  CASE WHEN cl.SPECODE=''OTOMATİK'' THEN ''OTOMATİK ÖDEME'' WHEN cl.SPECODE=''ZORUNLU'' THEN ''ZORUNLU CARİ'' END,
  CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE&lt;&gt;14 THEN clf.TRANNO ELSE inv.FICHENO END,
  cl.DEFINITION_,
  '''',
  '''',
  pt.DATE_,
  pt.TOTAL,
  pt.TRCURR,
  cur.CURCODE
  
  FROM LG_011_01_PAYTRANS pt
  INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
  LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
  LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
  
  WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE ''320%'' AND cl.SPECODE IN (''OTOMATİK'',''ZORUNLU'') AND cur.FIRMNR=11
  
  )
  
  UNION ALL
  
  (
  SELECT
   CASE d.SPECODE WHEN '''' THEN ''KREDİ'' WHEN ''FORWARD'' THEN ''FORWARD'' END [TÜR],
   d.CODE,
   f.DEFINITION_,
   g.DEFINITION_,
   d.NAME_,
   b.DUEDATE,
   a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
   d.TRCURR,
   e.CURCODE
  
   FROM
      (SELECT 
   PARENTREF,
   SUM(TOTAL) AS ANAPARA,
   SUM(INTTOTAL) AS FAIZ,
   SUM(BSMVTOTAL) AS BSMV,
   SUM(KKDFTOTAL) AS KKDF,
   SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
  
          FROM LG_011_BNCREPAYTR 
          WHERE TRANSTYPE = 0 
          GROUP BY PARENTREF) a 
  INNER JOIN (SELECT
   LOGICALREF,
   PARENTREF,
   CREDITREF,
   DUEDATE,
   OPRDATE
   FROM LG_011_BNCREPAYTR
                  WHERE TRANSTYPE = 0) b
  ON a.PARENTREF=b.PARENTREF
  
  LEFT OUTER JOIN (SELECT
   PARENTREF,
   SUM(TOTAL) AS ANAPARA,
   SUM(INTTOTAL) AS FAIZ,
   SUM(BSMVTOTAL) AS BSMV,
   SUM(KKDFTOTAL) AS KKDF,
   SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
          FROM LG_011_BNCREPAYTR 
          WHERE TRANSTYPE = 1 
          GROUP BY PARENTREF) c 
  ON b.LOGICALREF = c.PARENTREF
  
  INNER JOIN LG_011_BNCREDITCARD d
  ON b.CREDITREF=d.LOGICALREF
  
  INNER JOIN L_CURRENCYLIST e
  ON d.TRCURR=e.CURTYPE OR (d.TRCURR=0 AND e.CURTYPE=160)
  
  INNER JOIN LG_011_BNCARD f
  ON d.BNCRREF=f.LOGICALREF
  
  INNER JOIN LG_011_BANKACC g
  ON d.BNACCREF=g.LOGICALREF
  
  WHERE e.FIRMNR=11 AND a.TAKSIT - Isnull(c.TAKSIT,0)&lt;&gt;0
  )
  
  ) x
  
  PIVOT
  
  (
  
  SUM(AMOUNT)
  FOR CURCODE IN ('+ @cols +')
  
  ) xx
  
  ORDER BY xx.col6,xx.TRCURR, xx.col1, xx.col3, xx.col4, xx.col2
  '
  
  execute(@query)
  </code></pre>
  
  <hr>
  
  <p>and I get the following error when I execute the query:</p>
  
  <pre><code>Msg 102, Level 15, State 1, Line 205
  Incorrect syntax near ','.
  </code></pre>
  
  <p>I haven't been able to identify the problem so far. I don't think that there is a logical problem with my code as both parts of my code can run independently, but when I join them using a PIVOT operator, it fails. Could you please look through my code to see if I am making any mistakes in my reasoning or syntax?</p>
  
  <p>Thanks!</p>
  
  <hr>
  
  <p>UPDATE</p>
  
  <p>I know for a fact that my UNION ALLs work. Below is the code that goes under the PIVOT construct in my original code:</p>
  
  <pre><code>(
  
  SELECT
  'ÇEK' [col1],
  cs.NEWSERINO [col2],
  bn.DEFINITION_ [col3],
  ban.DEFINITION_ [col4],
  cl.DEFINITION_ [col5],
  cs.DUEDATE [col6],
  cs.AMOUNT [AMOUNT],
  cs.TRCURR [TRCURR],
  cur.CURCODE [CURCODE]
  
  FROM
  
  LG_011_01_CSTRANS a
  
  INNER JOIN
  
  (
  SELECT 
  CSREF,
  MAX(STATNO) [STATNO]
  
  FROM LG_011_01_CSTRANS
  GROUP BY CSREF) b
  ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
  
  INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
  INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
  INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
  INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF
  
  WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
  
  )
  
  UNION ALL
  
  (
  SELECT
  'LEASİNG',
   d.REGNR,
   cl.DEFINITION_,
   '',
   d.DESCRIPTION,
   c.PAYMENTDATE,
   a.KDVLI- Isnull(b.KDVLI,0),
   c.TRCURR,
   e.CURCODE
  
  
  FROM
  (SELECT
  LOGICALREF,
  SUM(PAYMENTTOTAL) AS KDVSIZ,
  SUM(INTTOTAL) AS FAIZ,
  SUM(MAINTOTAL) AS ANAPARA,
  SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
  SUM(VATINPAYMENTTOTAL) AS KDVLI
  
  FROM LG_011_LEASINGPAYMENTSLNS
  WHERE TRANSTYPE=0
  GROUP BY LOGICALREF) a
  
  LEFT OUTER JOIN
  
  (SELECT
  PARENTREF,
  SUM(PAYMENTTOTAL) AS KDVSIZ,
  SUM(INTTOTAL) AS FAIZ,
  SUM(MAINTOTAL) AS ANAPARA,
  SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
  SUM(VATINPAYMENTTOTAL) AS KDVLI
  FROM LG_011_LEASINGPAYMENTSLNS
  WHERE TRANSTYPE=1
  GROUP BY PARENTREF
  ) b 
  ON a.LOGICALREF= b.PARENTREF
  
  INNER JOIN
  LG_011_LEASINGPAYMENTSLNS c
  ON a.LOGICALREF=c.LOGICALREF
  
  
  INNER JOIN
  
  LG_011_LEASINGREG d
  ON c.LEASINGREF=d.LOGICALREF
  
  INNER JOIN
  LG_011_PURCHOFFER z
  ON c.LEASINGREF=z.LEASINGREF
  
  INNER JOIN
  (SELECT 
  MAX(LOGICALREF) LOGICALREF,
  LEASINGREF,
  CLIENTREF
  FROM LG_011_PURCHOFFER
  GROUP BY CLIENTREF,LEASINGREF) y
  ON z.LOGICALREF=y.LOGICALREF
  
  INNER JOIN LG_011_CLCARD cl
  ON z.CLIENTREF=cl.LOGICALREF
  
  INNER JOIN L_CURRENCYLIST e
  ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)
  
  
  WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)&lt;&gt;0
  
  )
  
  UNION ALL
  
  (
  
  SELECT
  CASE WHEN cl.SPECODE='OTOMATİK' THEN 'OTOMATİK ÖDEME' WHEN cl.SPECODE='ZORUNLU' THEN 'ZORUNLU CARİ' END,
  CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE&lt;&gt;14 THEN clf.TRANNO ELSE inv.FICHENO END,
  cl.DEFINITION_,
  '',
  '',
  pt.DATE_,
  pt.TOTAL,
  pt.TRCURR,
  cur.CURCODE
  
  FROM LG_011_01_PAYTRANS pt
  INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
  LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
  LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
  
  WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU') AND cur.FIRMNR=11
  
  )
  
  UNION ALL
  
  (
  SELECT
   CASE d.SPECODE WHEN '' THEN 'KREDİ' WHEN 'FORWARD' THEN 'FORWARD' END [TÜR],
   d.CODE,
   f.DEFINITION_,
   g.DEFINITION_,
   d.NAME_,
   b.DUEDATE,
   a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
   d.TRCURR,
   e.CURCODE
  
   FROM
      (SELECT 
   PARENTREF,
   SUM(TOTAL) AS ANAPARA,
   SUM(INTTOTAL) AS FAIZ,
   SUM(BSMVTOTAL) AS BSMV,
   SUM(KKDFTOTAL) AS KKDF,
   SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
  
          FROM LG_011_BNCREPAYTR 
          WHERE TRANSTYPE = 0 
          GROUP BY PARENTREF) a 
  INNER JOIN (SELECT
   LOGICALREF,
   PARENTREF,
   CREDITREF,
   DUEDATE,
   OPRDATE
   FROM LG_011_BNCREPAYTR
                  WHERE TRANSTYPE = 0) b
  ON a.PARENTREF=b.PARENTREF
  
  LEFT OUTER JOIN (SELECT
   PARENTREF,
   SUM(TOTAL) AS ANAPARA,
   SUM(INTTOTAL) AS FAIZ,
   SUM(BSMVTOTAL) AS BSMV,
   SUM(KKDFTOTAL) AS KKDF,
   SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
          FROM LG_011_BNCREPAYTR 
          WHERE TRANSTYPE = 1 
          GROUP BY PARENTREF) c 
  ON b.LOGICALREF = c.PARENTREF
  
  INNER JOIN LG_011_BNCREDITCARD d
  ON b.CREDITREF=d.LOGICALREF
  
  INNER JOIN L_CURRENCYLIST e
  ON d.TRCURR=e.CURTYPE OR (d.TRCURR=0 AND e.CURTYPE=160)
  
  INNER JOIN LG_011_BNCARD f
  ON d.BNCRREF=f.LOGICALREF
  
  INNER JOIN LG_011_BANKACC g
  ON d.BNACCREF=g.LOGICALREF
  
  WHERE e.FIRMNR=11 AND a.TAKSIT - Isnull(c.TAKSIT,0)&lt;&gt;0
  )
  </code></pre>
  
  <p>The code works by itself, but when I put it into the PIVOT structure, it fails. Please help! Or can you tell me a method that I can use instead of PIVOT that will give me the same result?</p>
  
  <hr>
  
  <p>UPDATE</p>
  
  <p>I think there is something wrong with the length of my query as when I run <code>print @query</code>, I get the following (Thanks @KumarHarsh for directing me to the correct way!):</p>
  
  <pre><code>SELECT
  
  col1 [TÜR],
  col2 [KOD],
  col3 [BANKA/CARİ],
  col4 [BANKA HESABI],
  col5 [AÇIKLAMA],
  col6 [VADE],
  [TL],[USD],[EUR]
  
  FROM
  
  (
  
  (
  
  SELECT
  'LEASİNG' [col1],
   d.REGNR [col2],
   cl.DEFINITION_ [col3],
   '' [col4],
   d.DESCRIPTION [col5],
   c.PAYMENTDATE [col6],
   a.KDVLI- Isnull(b.KDVLI,0) [AMOUNT],
   c.TRCURR [TRCURR],
   e.CURCODE [CURCODE]
  
  
  FROM
  (SELECT
  LOGICALREF,
  SUM(PAYMENTTOTAL) AS KDVSIZ,
  SUM(INTTOTAL) AS FAIZ,
  SUM(MAINTOTAL) AS ANAPARA,
  SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
  SUM(VATINPAYMENTTOTAL) AS KDVLI
  
  FROM LG_011_LEASINGPAYMENTSLNS
  WHERE TRANSTYPE=0
  GROUP BY LOGICALREF) a
  
  LEFT OUTER JOIN
  
  (SELECT
  PARENTREF,
  SUM(PAYMENTTOTAL) AS KDVSIZ,
  SUM(INTTOTAL) AS FAIZ,
  SUM(MAINTOTAL) AS ANAPARA,
  SUM(VATINPAYMENTTOTAL-PAYMENTTOTAL) AS KDV,
  SUM(VATINPAYMENTTOTAL) AS KDVLI
  FROM LG_011_LEASINGPAYMENTSLNS
  WHERE TRANSTYPE=1
  GROUP BY PARENTREF
  ) b 
  ON a.LOGICALREF= b.PARENTREF
  
  INNER JOIN
  LG_011_LEASINGPAYMENTSLNS c
  ON a.LOGICALREF=c.LOGICALREF
  
  
  INNER JOIN
  
  LG_011_LEASINGREG d
  ON c.LEASINGREF=d.LOGICALREF
  
  INNER JOIN
  LG_011_PURCHOFFER z
  ON c.LEASINGREF=z.LEASINGREF
  
  INNER JOIN
  (SELECT 
  MAX(LOGICALREF) LOGICALREF,
  LEASINGREF,
  CLIENTREF
  FROM LG_011_PURCHOFFER
  GROUP BY CLIENTREF,LEASINGREF) y
  ON z.LOGICALREF=y.LOGICALREF
  
  INNER JOIN LG_011_CLCARD cl
  ON z.CLIENTREF=cl.LOGICALREF
  
  INNER JOIN L_CURRENCYLIST e
  ON c.TRCURR=e.CURTYPE OR (c.TRCURR=0 AND e.CURTYPE=160)
  
  
  WHERE e.FIRMNR=11 AND z.STATUS=4 AND a.KDVLI - Isnull(b.KDVLI,0)&lt;&gt;0
  
  )
  
  UNION ALL
  
  (
  
  SELECT
  'ÇEK',
  cs.NEWSERINO,
  bn.DEFINITION_,
  ban.DEFINITION_,
  cl.DEFINITION_,
  cs.DUEDATE,
  cs.AMOUNT,
  cs.TRCURR,
  cur.CURCODE
  
  FROM
  
  LG_011_01_CSTRANS a
  
  INNER JOIN
  
  (
  SELECT 
  CSREF,
  MAX(STATNO) [STATNO]
  
  FROM LG_011_01_CSTRANS
  GROUP BY CSREF) b
  ON a.CSREF=b.CSREF AND a.STATNO=b.STATNO
  
  INNER JOIN LG_011_01_CSCARD cs ON a.CSREF=cs.LOGICALREF
  INNER JOIN LG_011_BANKACC ban ON cs.OURBANKREF=ban.LOGICALREF
  INNER JOIN LG_011_BNCARD bn ON ban.BANKREF=bn.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON cs.TRCURR=cur.CURTYPE OR (cs.TRCURR=0 AND cur.CURTYPE=160)
  INNER JOIN LG_011_CLCARD cl ON a.CARDREF=cl.LOGICALREF
  
  WHERE cs.DOC=3 AND cs.CURRSTAT=9 AND cur.FIRMNR=11
  
  )
  
  UNION ALL
  
  (
  
  SELECT
  CASE WHEN cl.SPECODE='OTOMATİK' THEN 'OTOMATİK ÖDEME' WHEN cl.SPECODE='ZORUNLU' THEN 'ZORUNLU CARİ' END,
  CASE WHEN pt.MODULENR=5 AND pt.TRCODE=14 THEN clf.DOCODE WHEN pt.MODULENR=5 AND pt.TRCODE&lt;&gt;14 THEN clf.TRANNO ELSE inv.FICHENO END,
  cl.DEFINITION_,
  '',
  '',
  pt.DATE_,
  pt.TOTAL,
  pt.TRCURR,
  cur.CURCODE
  
  FROM LG_011_01_PAYTRANS pt
  INNER JOIN LG_011_CLCARD cl ON pt.CARDREF=cl.LOGICALREF
  LEFT OUTER JOIN LG_011_01_INVOICE inv ON pt.FICHEREF=inv.LOGICALREF
  LEFT OUTER JOIN LG_011_01_CLFLINE clf ON pt.FICHEREF=clf.LOGICALREF
  INNER JOIN L_CURRENCYLIST cur ON pt.TRCURR=cur.CURTYPE OR (pt.TRCURR=0 AND cur.CURTYPE=160)
  
  WHERE pt.MODULENR IN (4,5) AND pt.PAID=0 AND pt.SIGN=1 AND cl.CODE LIKE '320%' AND cl.SPECODE IN ('OTOMATİK','ZORUNLU') AND cur.FIRMNR=11
  
  )
  
  UNION ALL
  
  (
  SELECT
   CASE d.SPECODE WHEN '' THEN 'KREDİ' WHEN 'FORWARD' THEN 'FORWARD' END [TÜR],
   d.CODE,
   f.DEFINITION_,
   g.DEFINITION_,
   d.NAME_,
   b.DUEDATE,
   a.TAKSIT - Isnull(c.TAKSIT,0) AS TAKSIT,
   d.TRCURR,
   e.CURCODE
  
   FROM
      (SELECT 
   PARENTREF,
   SUM(TOTAL) AS ANAPARA,
   SUM(INTTOTAL) AS FAIZ,
   SUM(BSMVTOTAL) AS BSMV,
   SUM(KKDFTOTAL) AS KKDF,
   SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
  
          FROM LG_011_BNCREPAYTR 
          WHERE TRANSTYPE = 0 
          GROUP BY PARENTREF) a 
  INNER JOIN (SELECT
   LOGICALREF,
   PARENTREF,
   CREDITREF,
   DUEDATE,
   OPRDATE
   FROM LG_011_BNCREPAYTR
                  WHERE TRANSTYPE = 0) b
  ON a.PARENTREF=b.PARENTREF
  
  LEFT OUTER JOIN (SELECT
   PARENTREF,
   SUM(TOTAL) AS ANAPARA,
   SUM(INTTOTAL) AS FAIZ,
   SUM(BSMVTOTAL) AS BSMV,
   SUM(KKDFTOTAL) AS KKDF,
   SUM(TOTAL+INTTOTAL+BSMVTOTAL+KKDFTOTAL) AS TAKSIT
          FROM LG_011_BNCREPAYTR 
  </code></pre>
  
  <p>There is something wrong because <code>@query</code> does not get all the data and I do not know how I can solve this issue. I would really appreciate if you could solve my problem!</p>
  
  <p>Thanks!</p>