Question

Photo of Michael Garrison

1

SQL: Help with a Pivot() query

3

Our accounting team has asked for a grid (table) summary for the financial batches rather than just the provided totals per Tender and per Account.

So say the different tender types are represented as column headers and the different accounts are the first column, each data cell would be the total of the contributions of that Tender type, to that Account.

Here's the simple query to get the list of totals per account and tender combination:

SELECT A.[Name] AS "Account Name", V.[Value] AS "Tender Type", SUM(D.Amount) AS "Total"
FROM [FinancialTransaction] T
    INNER JOIN [FinancialTransactionDetail] D ON T.[Id]=D.[TransactionId]
    INNER JOIN [FinancialAccount] A ON A.[Id] = D.[AccountId]
    INNER JOIN [DefinedValue] V ON V.[Id] = T.[CurrencyTypeValueId]
WHERE [BatchId]=@BatchId AND V.[DefinedTypeId]=10
GROUP BY V.[Value], A.[Name]
ORDER BY A.[Name], V.[Value]

Now, as I understand it, I need to take V.[Value] out of this initial query, then Pivot() in the tender types to aggregate (via Sum()) D.[Amount].

But I've never used Pivot() before and can't seem to get it to work. Are there any SQL gurus out there who can spot my issue or point me in the right direction?

Here's what I tried:

SELECT A.[Name], D.Amount
    FROM [FinancialTransaction] T
        INNER JOIN [FinancialTransactionDetail] D ON T.[Id]=D.[TransactionId]
        INNER JOIN [FinancialAccount] A ON A.[Id] = D.[AccountId]
    WHERE T.[BatchId]=150
    GROUP BY V.[Value], A.[Name]
PIVOT(SUM(D.Amount) FOR V.[Value] IN (
                                        SELECT DISTINCT V.[Value]
                                            FROM [FinancialTransaction] T
                                                INNER JOIN [DefinedValue] V ON V.[Id] = T.[CurrencyTypeValueId]
                                            WHERE T.[BatchId]=150 AND V.[DefinedTypeId]=10
                                        )
)

I thought maybe the issue was in specifying a subquery for the IN() instruction (apparently SQL Server didn't used to be able to handle that) but I also tried hard-coding

IN("Cash","Check")

and I got the same syntax error (near "Pivot").

  • Photo of Joe Carroll

    0

    Sounds like you can't dynamically set the values in the IN() part of the PIVOT, but you can build the SQL on the fly and then execute.  There's probably an easier solution, but I think it produces the report that you want.

    Anyway, the first query grabs all of the tenders that are present for the given batch and then strings them together.  In my case, the result was '[Cash],[Check]'.  That value is placed in @types and then the final query (@query) is built based on the PIVOT syntax plus @types.  Final step is to execute the dynamic query.

    DECLARE @types AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

    SELECT @types= ISNULL(@types + ',','') 
           + QUOTENAME([Value])
    FROM (SELECT DV.[Value]
           FROM DefinedValue DV
          WHERE EXISTS (SELECT 1 
                          FROM FinancialTransaction FT
                           WHERE DV.[ID] = FT.[CurrencyTypeValueId]
                           AND FT.[BatchId] = 12
                   )) AS TenderTypes
    ;
    SET @query =  
     'SELECT *
      FROM
      (
    SELECT A.[Name] AS FundName
          , V.[Value] AS TenderType
          , D.[Amount] AS GivingAmount   
    FROM [FinancialTransaction] T
        INNER JOIN [FinancialTransactionDetail] D ON T.[Id]=D.[TransactionId]
        INNER JOIN [FinancialAccount] A ON A.[Id] = D.[AccountId]
        INNER JOIN [DefinedValue] V ON V.[Id] = T.[CurrencyTypeValueId]
    WHERE [BatchId]=12 AND V.[DefinedTypeId]=10
    ) P
    PIVOT
    (SUM(GivingAmount) FOR TenderType IN (' + @types + ')
    ) AS PVT
    ORDER BY FundName'
    ;

    execute(@query);

    • Michael Garrison

      Michael Garrison

      In Power Tools, this works great. But it doesn't work when I try to feed it the batchId via the URL parameter @batchId.


      I've run into this issue before trying to build a compound query like this for this use- it seems that variables can only be used in a single query...? If I have just one query calling @batchId, it works. As soon as I add a second one, it throws 'Query Error! Must declare the scalar variable "@bid"."='


      I tried declaring another variable @bid and using SET @bid=@batchId, but it looks like the same limitation applies- @bid works once but not a second time, and once I've set @bid, @batchId is spent as well.


      =/

      • Michael Garrison

        Michael Garrison

        Got it licked with your code, THANK YOU! Instead of using parameters, I stored the parameter in a temporary table and used a SELECT statement instead of calling the parameter:


        SELECT [value] INTO #tempBatch FROM (SELECT @batchId AS 'value') AS x;
        DECLARE @types AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);


        SELECT @types= ISNULL(@types + ',','')
        + QUOTENAME([Value])
        FROM (SELECT DV.[Value]
        FROM DefinedValue DV
        WHERE EXISTS (SELECT 1
        FROM FinancialTransaction FT
        WHERE DV.[ID] = FT.[CurrencyTypeValueId]
        AND FT.[BatchId] = (SELECT [value] FROM #tempBatch)
        )) AS TenderTypes
        ;
        SET @query =

        'SELECT *
        FROM
        (
        SELECT A.[Name] AS FundName
        , V.[Value] AS TenderType
        , D.[Amount] AS GivingAmount

        FROM [FinancialTransaction] T
        INNER JOIN [FinancialTransactionDetail] D ON T.[Id]=D.[TransactionId]
        INNER JOIN [FinancialAccount] A ON A.[Id] = D.[AccountId]
        INNER JOIN [DefinedValue] V ON V.[Id] = T.[CurrencyTypeValueId]
        WHERE [BatchId]=(SELECT [value] FROM #tempBatch) AND V.[DefinedTypeId]=10
        ) P
        PIVOT
        (SUM(GivingAmount) FOR TenderType IN (' + @types + ')
        ) AS PVT
        ORDER BY FundName'
        ;


        execute(@query);

        • Michael Garrison

          Michael Garrison

          Joe,
          This stopped working with v4.0 (error: Invalid column name CurrencyTypeValueId). I tried troubleshooting myself but didn't get very far since I can't quite make out how, exactly, this query works =) I verified that CurrencyTypeValueId is still a column in the "FinancialTransaction" table, and that's about as far as I got. Any ideas?


          Thanks

          • David Turner

            David Turner

            Michael, v4.0 actually moved the CurrencyTypeValueId from the FinancialTransaction table to a sub FinancialPaymentDetail table so you'd need to update the first part of that query like so...


            ...
            FROM FinancialTransaction FT
            INNER JOIN FinancialPaymentDetail PD ON PD.[Id] = FT.[FinancialPaymentDetailId]
            WHERE DV.[ID] = PD.[CurrencyTypeValueId]
            ...


            -David

            • Michael Garrison

              Michael Garrison

              Wonder why I thought I saw that column. Oh well.


              You're quite right, that plus similar logic near the end worked well. Final working code again:


              SELECT [value] INTO #tempBatch FROM (SELECT @batchId AS 'value') AS x;
              DECLARE @types AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);


              SELECT @types= ISNULL(@types + ',','')
              + QUOTENAME([Value])
              FROM (SELECT DV.[Value]
              FROM DefinedValue DV
              WHERE EXISTS (SELECT 1
              FROM FinancialTransaction FT
              INNER JOIN FinancialPaymentDetail PD ON PD.[Id] = FT.[FinancialPaymentDetailId]
              WHERE DV.[ID] = PD.[CurrencyTypeValueId]
              AND FT.[BatchId] = (SELECT [value] FROM #tempBatch)
              )) AS TenderTypes
              ;
              SET @query =

              'SELECT *
              FROM
              (
              SELECT A.[Name] AS AccountName
              , V.[Value] AS TenderType
              , D.[Amount] AS GivingAmount

              FROM [FinancialTransaction] T
              INNER JOIN [FinancialTransactionDetail] D ON T.[Id]=D.[TransactionId]
              INNER JOIN [FinancialAccount] A ON A.[Id] = D.[AccountId]
              INNER JOIN [FinancialPaymentDetail] PD ON [PD].[Id] = T.[FinancialPaymentDetailId]
              INNER JOIN [DefinedValue] V ON V.[Id] = PD.[CurrencyTypeValueId]
              WHERE [BatchId]=(SELECT [value] FROM #tempBatch) AND V.[DefinedTypeId]=10
              ) P
              PIVOT
              (SUM(GivingAmount) FOR TenderType IN (' + @types + ')
              ) AS PVT
              ORDER BY AccountName'
              ;


              execute(@query);


              Edit: I meant to add: THANK YOU, THANK YOU, THANK YOU, THANK YOU! This query, as I mentioned, is quite over my head.

            • Photo of Joe Carroll

              1

              I work on a DBMS without a cool PIVOT() function so we have to do it differently.  I think this is what you are asking for, but you would have to add a SUM() for each Tender type.

              SELECT A.[Name]
                    , SUM(CASE WHEN V.[Value] = 'Cash' then D.Amount ELSE 0 END) AS "Cash Amt"
                    , SUM(CASE WHEN V.[Value] = 'Check' then D.Amount ELSE 0 END) AS "Check Amt"      
              FROM [FinancialTransaction] T
                  INNER JOIN [FinancialTransactionDetail] D ON T.[Id]=D.[TransactionId]
                  INNER JOIN [FinancialAccount] A ON A.[Id] = D.[AccountId]
                  INNER JOIN [DefinedValue] V ON V.[Id] = T.[CurrencyTypeValueId]
              WHERE [BatchId]=12 AND V.[DefinedTypeId]=10
              GROUP BY A.[Name]
              ORDER BY A.[Name]

              • Michael Garrison

                Michael Garrison

                Thanks! This does well at dynamically showing only the accounts which had transactions, but I'd really like to have the columns also be dynamic based on the tenders actually utilized in the batch. Otherwise I'd have about 20 columns in every batch, most of which would be blank, and which would require me to add their logic into the page every time our accountant changed one.


                But I'll definitely keep this in mind in case I can't figure out Pivot

              • Photo of Arran France

                0

                Michael, have you got this sorted or do you still need someone to take a look?

                • Michael Garrison

                  Michael Garrison

                  I'd love to be able to use Pivot to create a really dynamic table displaying only the accounts and tenders utilized in a given batch. So I'm holding out hope that someone can advise me where my pivot went wrong. =)