Pivot in SQL?

[AccessNub]

I am normally able to figure this stuff out on my own, but I can't seem to get the logic for a SQL pivot.

Here is what I have in Access:

TRANSFORM Sum(Table.PaymentAmt) AS Amt
SELECT RptDate
FROM Table
GROUP BY Table.RptDate
ORDER BY Table.RptDate DESC 
PIVOT Table.PlanType;

Which outputs like this:
RptDate Plan1 Plan2
2019-08-25 $436.00 $4,255.24
2019-08-24 $1,167.38 $4,965.16

Can anyone SQLize this? I'm too embarassed to post what I have been trying to do in SQL, it looks like a pile of dog crap.

 

[theDBguy]

Hi. Not sure I understand the question. Isn’t the code you posted above already the SQL representation of your pivot?

 

[AccessNub]

Ah, I wasn't clear. I want this query to work in Sql Management Studio. It doesn't like the word Pivot.

 

[isladogs]

SQL Server should allow the use of PIVOT from version 2005 onwards!

Check this article and the links in it https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

 

[AccessNub]

Ok, I got it, I sat down and really focused on how to build this damn thing..Turns out in SQL you have to name all the possible values.


SELECT RptDate,[PR] as PR, [TR] as TR,[TY] as TY FROM (SELECT RptDate,PlanType, PaymentAmt from TABLE) ps
PIVOT (Sum (PaymentAmt) for PlanType IN ([PR], [TR], [TY])) as pvt ORDER BY RptDate DESC

 

[isladogs]

Glad you now have a solution ….but are you sure you need to 'alias' fields as the same name?
Did you look at the dynamic pivot link in that article?

 

[AccessNub]

I actually did, it failed without it. I even had to alias the pivot and the table even though I wasn't referencing it anywhere else.

 

[theDBguy]

Ok, I got it, I sat down and really focused on how to build this damn thing..Turns out in SQL you have to name all the possible values.

SELECT RptDate,[PR] as PR, [TR] as TR,[TY] as TY FROM (SELECT RptDate,PlanType, PaymentAmt from TABLE) ps
PIVOT (Sum (PaymentAmt) for PlanType IN ([PR], [TR], [TY])) as pvt ORDER BY RptDate DESC

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.