Click here to Skip to main content
13,836,661 members

Web Development

 
PinnedHow to get an answer to your question Pin
Chris Maunder4-Sep-10 3:25
adminChris Maunder4-Sep-10 3:25 
PinnedHOW TO ANSWER A QUESTION PinPopular
Chris Maunder12-Jul-09 23:40
adminChris Maunder12-Jul-09 23:40 
QuestionCore 2.1 Razor Pages Solution Pin
John Simmons / outlaw programmer22-Jan-19 2:52
mveJohn Simmons / outlaw programmer22-Jan-19 2:52 
QuestionBS Carousel local images Pin
Member 1097549721-Jan-19 8:46
memberMember 1097549721-Jan-19 8:46 
AnswerRe: BS Carousel local images Pin
Pete O'Hanlon22-Jan-19 3:03
protectorPete O'Hanlon22-Jan-19 3:03 
GeneralRe: BS Carousel local images Pin
Member 109754974hrs 50mins ago
memberMember 109754974hrs 50mins ago 
QuestionCreating a 'design your own' feature on my website Pin
Member 1411577011-Jan-19 18:27
memberMember 1411577011-Jan-19 18:27 
AnswerRe: Creating a 'design your own' feature on my website Pin
Nathan Minier16-Jan-19 2:45
professionalNathan Minier16-Jan-19 2:45 
QuestionCreating PIVOT data not producing correct result Pin
samflex10-Jan-19 21:31
membersamflex10-Jan-19 21:31 
Greetings,

I am attempting to create PIVOT data where amountPaid is displayed under registration_Date as in the following format
:
2018 2019, etc
300.00 160.00. etc

So far, the result is not coming out right.
Instead of the amountPaid displaying on the year colums, reg_fee amount is displaying on the first column.

This is driving me nuts.

I am creating the pivot data dynamically. This way, any new year created is displayed with amountPaid as the value.

This is the code I am using:

ALTER PROCEDURE [dbo].[uspGetPivotedData]
AS
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(m.registration_date) 
            FROM Members m
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT TransactionID,
                AmountOwed,
				FullName,
				Gender,
				phone,
				EmailAddress,
				Address,
				EventYear, 
                Description,
				Reg_Fee
				 ' + @cols + ' from 
               (
                SELECT t.TransactionID, 
					 t.AmountOwed,
					 m.FullName,
                     m.EmailAddress,
					 m.Gender, 
					 m.Phone, 
                     m.Address,
				     t.EventYear, 
                     p.Description,					
					 Reg_Fee,
                     m.registration_date,
					 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
          ) x
            pivot 
            (
                max(AmountPaid)
                for registration_date in (' + @cols + ')
            ) p '
            EXECUTE sp_executesql @query


registration_date is of int data type.

Any ideas what I am doing wrong?
AnswerRe: Creating PIVOT data not producing correct result Pin
Richard Deeming11-Jan-19 3:01
mveRichard Deeming11-Jan-19 3:01 
General(Solved) Re: Creating PIVOT data not producing correct result Pin
samflex11-Jan-19 5:14
membersamflex11-Jan-19 5:14 
QuestionObject Type and HTTP JSON Gets Pin
cjb1107-Jan-19 23:04
membercjb1107-Jan-19 23:04 
QuestionWordpress Website Showing Error Pin
Jack_Calder5-Jan-19 1:48
professionalJack_Calder5-Jan-19 1:48 
AnswerRe: Wordpress Website Showing Error Pin
Afzaal Ahmad Zeeshan5-Jan-19 2:51
mveAfzaal Ahmad Zeeshan5-Jan-19 2:51 
Questiontransform: skew only on one side [SOLVED] Pin
Valentinor17-Dec-18 3:49
memberValentinor17-Dec-18 3:49 
AnswerRe: transform: skew only on one side Pin
Valentinor17-Dec-18 6:40
memberValentinor17-Dec-18 6:40 
GeneralRe: transform: skew only on one side Pin
Richard Deeming17-Dec-18 10:26
mveRichard Deeming17-Dec-18 10:26 
GeneralRe: transform: skew only on one side Pin
Valentinor17-Dec-18 12:02
memberValentinor17-Dec-18 12:02 
QuestionMessage Closed Pin
14-Dec-18 3:57
memberMember 1408965614-Dec-18 3:57 
QuestionTrouble w/ Background Image Load Time Pin
Member 1208020111-Dec-18 12:35
memberMember 1208020111-Dec-18 12:35 
AnswerRe: Trouble w/ Background Image Load Time Pin
Nathan Minier12-Dec-18 5:23
professionalNathan Minier12-Dec-18 5:23 
AnswerMessage Closed Pin
6-Dec-18 5:01
memberAshishKhuraishy6-Dec-18 5:01 
AnswerMessage Closed Pin
6-Dec-18 4:59
memberAshishKhuraishy6-Dec-18 4:59 
QuestionHow to find server name Pin
_Flaviu29-Nov-18 9:48
member_Flaviu29-Nov-18 9:48 
GeneralRe: How to find server name Pin
Richard MacCutchan29-Nov-18 10:58
protectorRichard MacCutchan29-Nov-18 10:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190114.1 | Last Updated 23 Jan 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid