Passing a parameter into a Sql Pivot

Question

I'm having some trouble passing multiple parameters into a view I wish to Pivot. I am trying to implement this into a report I am creating that is constantly having new accounts added to it. I am sure this has to be done as a stored procedure, but I have limited knowledge and I am not finding much in the ways of explaining how to do this. Below is just a small fraction of what I need to do, but the idea is the same.

declare @AccountRef_Fullname nvarchar(max)

select  @AccountRef_Fullname = (select distinct Accountref_fullname 
from (select accountref_fullname from journalcreditlinedetail)JournalCreditLine
union
(select accountref_fullname from journaldebitlinedetail)
union
(select accountref_fullname from txnexpenselinedetail)
union
(select accountref_fullname from depositlinedetail)
union
(select discountaccountref_fullname from [appliedtotxndetail]))



select * from

(SELECT     DATEPART(ww, JournalEntry_2.TxnDate) AS Week, DATEPART(YYYY, JournalEntry_2.TxnDate) AS Year, SUM([Credit-Debit].Amount) 
                                                                           AS Amount, [Credit-Debit].AccountRef_FullName
                                                    FROM          (SELECT     IDKEY, sum(isnull(Amount,0)) * - 1 AS Amount, AccountRef_FullName
                                                                            FROM          dbo.journalcreditlinedetail
                                                                            group by idkey, AccountRef_FullName
                                                                            UNION
                                                                            SELECT     IDKEY, sum(isnull(Amount,0))Amount, AccountRef_FullName
                                                                            FROM         dbo.journaldebitlinedetail
                                                                            group by idkey, AccountRef_FullName
                                                                            ) AS [Credit-Debit] INNER JOIN
                                                                               (SELECT     TxnID, TxnDate
                                                                                 FROM          dbo.journalentry AS journalentry_1) AS JournalEntry_2 ON [Credit-Debit].IDKEY = JournalEntry_2.TxnID
                                                    GROUP BY [Credit-Debit].AccountRef_FullName, DATEPART(ww, JournalEntry_2.TxnDate), DATEPART(yyyy, JournalEntry_2.TxnDate)
                                                    ) Journal_Data


                                                    PIVOT
(
  sum(amount)
  for
  AccountRef_FullName in (' + @AccountRef_Fullname + ')
  )
 AS PivotTable

you might want to get your @AccountRef_Fullname parameter fixed first. That should be throwing an Subquery returned more than 1 value error if you have more than one account — JamieD77
– JamieD77, Commented Jun 16, 2015 at 17:33
Thank you for your quick response! I have approximately 200 accounts. I need to have each of them as a column header. The only error I've received is. "Incorrect syntax near ' + @AccountRef_Fullname + '." — Stephan Rieber
– Stephan Rieber, Commented Jun 16, 2015 at 18:17

JamieD77 · Accepted Answer · 2015-06-16 18:48:00Z

1

Dynamic Pivot statements need to formed and executed using EXEX(@sql). Column names have to be specified in the Select and In portion of the query and can be a variable.

To build the column names you need to wrap each Accountref_fullname in []

DECLARE @AccountRef_Fullname NVARCHAR(MAX)
SELECT @AccountRef_Fullname = COALESCE(@AccountRef_Fullname + ',', '') + '[' + AccountRef_Fullname + ']'
FROM (
    SELECT accountref_fullname FROM journalcreditlinedetail
    UNION select accountref_fullname FROM journaldebitlinedetail
    etc..
)

Then build your select statement and execute it like

DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N' SELECT Week, Year, ' + @AccountRef_Fullname 
    + 'FROM ( your subquery ) JournalData'
    + 'PIVOT ('
    + ' SUM(amount) FOR AccountRef_FullName IN (' + @AccountRef_Fullname + ')'
    + ') AS PivotTable'

EXEC (@Sql)

answered Jun 16, 2015 at 18:48

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephan Rieber Over a year ago

Thank you for your guidance. I am learning quite a bit from this and things are making more sense to me now, but I am running into one issue. When I execute the procedure I get an incorrect syntax near the keyword select (This is the select @accountref_fullname = coalese......) But when I run the statements by themselves they are executed successfully.

Collectives™ on Stack Overflow

Passing a parameter into a Sql Pivot

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related