PIVOT query is not working correctly

Transact-SQL
1

Greetings experts,

I am using the following stored procedure to create PIVOT table.

In this stored proc, year (eventYear) is created dynamically from transactions table.
The results are stored in a table called MemberHistory.

So far, the query is generating results.

The only issue I am so far is that value of Amount is stored in eventYear in the format of yyyy.

For instance, we could have value of eventYear as following:

2016, 1017, 2018, 2019

and max amount for each year is stored in each of the years as:

2016 2017 2018 2019
390.00 219.00 590.00 120.00

For some reason, the value of Amount are not getting stored in the years.

They are showing of as NULL

Any ideas what I am doing wrong?

Thanks in advance for your assistance

Here is exact code I am using:

ALTER PROCEDURE [dbo].[uspGetPivotedData]
AS
drop table MemberHistory;
DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(m.date_registered,'yyyy')) 
        FROM Members m
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
set @query = 'SELECT
	memberName,
		eventYear,
		envelopeNumber,
		registrationFee,
		balance,
		' + @cols + ' INTO dbo.MemberHistory from 
           (
            SELECT
	               m.memberName,
		               t.eventYear,
					   t.eventYear,
		               t.envelopeNumber,
		               t.registrationFee, 
					   t.Balance,
                   t.Amount
                 from dbo.Transactions AS t INNER JOIN dbo.Members AS m ON t .MemberID = m.MemberID INNER JOIN
                 dbo.PaymentTypes AS p ON t .TypeID = p.PaymentTypeID
      ) x
        pivot 
        (
            max(Amount)
            for date_registered in (' + @cols + ')
        ) p '
        EXECUTE sp_executesql @query
2

Get the query string that is being executed.
If you can't see from that what the issue is then post that query.
I always create a trace table and save entries to that so that I can monitor what is goning on in the system and diagnose issues as in:
http://www.nigelrivett.net/Products/DWBuilder/TraceTable.html

3

Thanks a lot for your response.

I don't understand what you mean by query string.

This is what I use for testing:

DECLARE	@return_value int

EXEC	@return_value = [dbo].[uspGetPivotedData]

SELECT	'Return Value' = @return_value

GO
4

The query string is the dynamic sql string that you are executing.
The thing in @query.

If you have a problem with executing an sql string that you have built the first thing to do is to check that the string is what you expect it to be.

If it isn't obvious from that then execute the string in a query window and make changes until it does what you want then change the SP to build it correctly.

5

I think you r are aiming to get something like

declare @t table (m varchar(10), y int, a int)
insert @t select 'a', 2010, 10
insert @t select 'a', 2010, 20
insert @t select 'a', 2011, 10
insert @t select 'b', 2011, 10

select m, [2010],[2011]
from (select m,y,a from @t) a
pivot
(max(a) for y in ([2010],[2011])) p

6

Are you sure it works.
I would expect date_registered to appear in the select - but this might be a format I don't use.

7

My sincere apologies. I was forced into an errand.

The issue is not getting the value of registered_dates. That works. The issue is pivoting the value of Amount to become a value of date_registered.

Please see following illustrations.

Members table
MemberID   MemberName   date_Registered
3	   Ken Norton	2019-03-15

PamentTypes table:
PaymentTypeID                  Description
4                              Annual Dues


Transactions table
TransactioID   MemberID  TypeID  Amount   AmountPaid Balance   RegistrationFee EnvelopNumber EeventYear 
4	        3	  4	60.0000	  0.0000     60.0000	0.0000	       E0002	     2019-03-17

From the code posted, when these columns are queried, their values are stored in a table called MemberHistory.

Here is the values on that table.

MemberHistory table:
MemberName      eventYear   EnvelopeNumber   RegistrationFee   Balance  2019
Ken Norton	2019-03-17  E0002	     0.0000	      60.0000	NULL

As you can see the Year's value is being queried correctly. It is the value of SUM(Amount)that is supposed to be under 2019.

Now, it is NULL.

My point is that yes, you are correct that I am aiming to get Year number (eg 2019) and the value of Amount (in this case 60.000).

That value should from Transactions table based on the JOINS and inserted into MemberHistory.

Not sure what I am doing wrong but just not working.

Thanks a lot for all your help

8

Have you tried checking the query string that you are executing like I suggested. That should enable you to identify and correct the issue.
As I said I don't see how date_registered is defined and that is used to calculate the max(amount) so would cause the problem with that value.

9

To help further to aid you in how to diagnose these sorts of things.
If you get the query string I think you will find you are executing something like this (creating a table but this the the query used)
And I've created table variables so you can play with the query.
You should find that it gives an error for date_registered as that doesn't appear anywhere in the data to be pivoted.
Your underlying issue is that you are using the year to aggregate for the output column but don't have the year appearing anywhere in the data.


declare @Members table (MemberID int, MemberName varchar(20),  date_Registered datetime)
insert @Members select 3, 'Ken Norton', '20190315'

declare @PaymentTypes table (PaymentTypeID int, Description varchar(20))
insert @PaymentTypes select 4, 'Annual Dues'

declare @Transactions table (TransactioID int, MemberID int, TypeID int, Amount float, AmountPaid int, Balance float, RegistrationFee float, EnvelopeNumber varchar(20), EventYear datetime)
insert  @Transactions select 4,3,4,60,0, 60, 0, 'E002', '20190317'

select STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(m.date_registered,'yyyy')) 
        FROM @Members m
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')


SELECT
	memberName,
		eventYear,
		envelopeNumber,
		registrationFee,
		balance,
		[2019] from 
           (
            SELECT
	               m.memberName,
		               t.eventYear,
		               t.envelopeNumber,
		               t.registrationFee, 
					   t.Balance,
                   t.Amount
                 from @Transactions AS t INNER JOIN @Members AS m ON t .MemberID = m.MemberID INNER JOIN
                 @PaymentTypes AS p ON t .TypeID = p.PaymentTypeID
      ) x
        pivot 
        (
            max(Amount)
            for date_registered in ([2019])
        ) p

I think the correct query would be more like


SELECT
	memberName,
		eventYear,
		envelopeNumber,
		registrationFee,
		balance,
		[2019] from 
           (
            SELECT
	               m.memberName,
		               t.eventYear,
		               t.envelopeNumber,
		               t.registrationFee, 
					   t.Balance,
                   t.Amount ,
					   date_registered = year(m.date_Registered)
                 from @Transactions AS t INNER JOIN @Members AS m ON t .MemberID = m.MemberID INNER JOIN
                 @PaymentTypes AS p ON t .TypeID = p.PaymentTypeID
      ) x
        pivot 
        (
            max(Amount)
            for date_registered in ([2019])
        ) p
10

Hi,

Thanks a lot for your help.

However, I don't know why you had to hardcode the year value.

That's precisely why we wanted it to be dynamic so the value of year can be generated automatically generated depending on the year.

If you replace the hardcoded value with date_registered, you run into same issue.

In any case, I have come up with a solution that I hope can help someone else in the future.

Here it is:

ALTER PROCEDURE [dbo].[uspGetPivotedData]
AS
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear))
            FROM Transactions t
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
 
set @query = N'WITH CTE AS
(
    SELECT
        t.TransactionID, 
        m.memberName,
        m.date_registered, 
        envelopeNumber,
        registrationFee,
        t.Balance,
        YEAR(eventyear) eventyear,
        t.AmountPaid
    FROM
        dbo.Transactions AS t 
        INNER JOIN dbo.Members AS m 
        ON t .MemberID = m.MemberID 
        INNER JOIN dbo.PaymentTypes AS p 
        ON t .TypeID = p.PaymentTypeID
)
SELECT
    TransactionID,
    memberName,
    date_registered, 
    envelopeNumber,
    registrationFee,
    Balance, 
' + @cols + N'
FROM
    CTE
PIVOT
    (
        MAX(AmountPaid)
        FOR eventyear IN (' + @cols + N')
    ) p 
ORDER BY 
    TransactionID';
EXECUTE sp_executesql @query;EXECUTE sp_executesql @query

This works perfectly!

11

Think you still don't understand the approach - look at my first post again. That gave a method of diagnosing issues with this sort of issue and would have enabled you to spot the problem.

My post was showing what your query string looked like - basically you are generating the column name and creating the dynamic query string.
Had you looked a that query string you probably would have realised that the column was missing from the derived table (I even tried to point out that date_registered was missing and it was probably getting an error).
Had you executed the query you would have got an error which would again have made it obvious what was wrong.
The final query I gave was how you needed to change the built string to get it to work. Basically adding the entry to the select part.

I would suggest you go through the diagnosis part again so that you understand what to do in future.

12

Please look at my original query again and then look at the FINAL solution.

The ONLY difference is that I replace date_registered with eventYear and then replaced eventYear with date_registered.

In other words, instead of date_registered showing values like:

2016 2017 2018 2019,

I am using eventYear now to do same.

If I switch things back and replace eventYear with date_registered, like eventYear, date_registered will show up only ONCE.

The difference year is that I am using CTE as temp table to store eventYear and AmountPaid among others (This used to be Amount).

13

Still don't think you understand the point but I give up.
Maybe if you read through the thread again and do what I suggested you will understand the process and the fundamentals of what you are trying to do.

Note - what you have now isn't what you said you wanted.