CODEWITHSUNDEEP
sql-serversql-server-2008-r2pivot
Jase Villam
Jase Villam

Reputation: 3135

SQL Server 2008 R2: Invalid column name in PIVOT query

I have a dynamic pivot query and I want to pivot on businessaccountnumber which I know exist in the transaction_table but it keeps returning that "Invalid column name 'bizi'."- for line 1.

DECLARE @Output nvarchar(max) = N''
DECLARE @PivotList varchar(max)


SELECT
  @PivotList = COALESCE(@PivotList + ', ', N'') + N'[' + bizid + N']'

FROM (SELECT DISTINCT
  BusinessAccountNumber [bizid]
FROM transaction_table
WHERE postingdate BETWEEN '1/01/2015' AND '2/01/2015'
) AS CustProds;

SET @Output = 'SELECT [bizName],[bizi]
    , ' + @PivotList + '
FROM  ( select businessname as [bizName],businessaccountnumber as [bizi],
        sum((Transactionamount*(-1))) as [Transactionamount] 
        FROM transaction_table
        WHERE postingdate between ''1/01/2015'' and ''2/01/2015''
        GROUP BY businessaccountnumber,businessname) as P
PIVOT ( SUM(Transactionamount) FOR P.bizi IN (' + @PivotList + ') ) AS PVT'

EXEC sp_executesql @Output;

EDIT: Thanks to Backs for pointing out my error but now I have a new error.

the output I am looking for is:

Date      | bizid12| bizid13| bizid14...
01/01/2015|  $1    | $3     | $56
01/02/2015| $12    | $34    | $3
.....


DECLARE @Output nvarchar(max) = N''
DECLARE @PivotList varchar(max)


SELECT
  @PivotList = COALESCE(@PivotList + ', ', N'') + N'[' + bizid + N']'

FROM (SELECT DISTINCT
  BusinessAccountNumber [bizid]
FROM transaction_table
WHERE postingdate BETWEEN '1/01/2015' AND '2/01/2015'
) AS CustProds;

SET @Output = 'SELECT [sp_date]
    , ' + @PivotList + '
FROM  ( select Convert(varchar,postingdate,101) as [sp_date] 
        ,businessaccountnumber as [bizi],
        sum((Transactionamount*(-1))) as [Transactionamount] 
        FROM transaction_table
        WHERE postingdate between ''1/01/2015'' and ''2/01/2015''
        GROUP BY businessaccountnumber) as P
PIVOT ( SUM(Transactionamount) FOR P.bizi IN (' + @PivotList + ') ) AS PVT'

EXEC sp_executesql @Output;

Error now says:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Qry12090'.

Msg 1056, Level 15, State 1, Line 1
The number of elements in the select list exceeds the maximum allowed number of 4096 elements.

Upvotes: 0

Views: 792

Answers (1)

Backs
Backs

Reputation: 24913

You can't select bizi in query SELECT [bizName],[bizi] using it in PIVOT FOR P.bizi IN. Remove [bizi] from select statement

Upvotes: 1

Related Questions