PLEASE HELP - Pivot just not working

Transact-SQL
1

Firstly I must admit I learned t-sql on my own and my syntax I know is not the best as I use temp tables, but other than that, I am stumped. Every time I make a small change to fix one error, I get 3 more. It's been 4 hours and I just cannot get it to work.

Here is the temp table I've created in my sp where it references other temp tables in my sp (I know it's not the best way to do this but I digress):

Create Table #SG_MonthlyTotals(
GLAcct varchar(12),
GLDescription varchar(50),
AcctType varchar(5),
MainAccount varchar(5),
Location varchar(2),
Division varchar(3),
ContractType varchar(10),
Category varchar(50),
SubCategory varchar(50),
Mth date,
MonthlyBalance money,
MonthlyBudget money,
MonthlyBalancePY money
)
insert into #SG_MonthlyTotals
select a.*,m.Mth,m.MonthlyBalance,b.MonthlyBudget,p.MonthlyBalancePY
from #SG_GLAccounts a left outer join #SG_MonthlyBalances m
on a.GLAcct=m.GLAcct left outer join #SG_MonthlyBudgets b
on m.GLAcct=b.GLAcct and m.Mth=b.Mth left outer join #SG_MonthlyBalancesPY p
on a.GLAcct=p.GLAcct

I have 1 date parameters for the sp: @StartDate DateTime

I want to pivot 12 months of data and aggregate the MonthlyBalance amount. I need all the other columns then have the MonthlyBalance pivot for each month. The months are Dynamics, so I've used DateAdd to start at the @StartDate then add 11 months. I've done it the long way but it just makes more sense to me.

I tried it without the Convert and received an error but now still getting these 2 errors. I've tried everything. After some changes I get rid of the convert error but then get an error about the Mth being referenced twice. The Mth is the field I want to Pivot. The values in the db are DateTime and it has 2015-01-01 (the year and month change but the day is always 1 for every record so that's why I was adding a month).

Please help if you can. I had to put the [] around the dates at one point due to some other error.

Msg 8114, Level 16, State 1, Procedure SG_FinancialReports_Budgets_YTD, Line 107
Error converting data type nvarchar to date.
Msg 473, Level 16, State 1, Procedure SG_FinancialReports_Budgets_YTD, Line 107
The incorrect value "Convert(date,@StartDate)" is supplied in the PIVOT operator.

My code:

select *,Convert(date,@StartDate),DateAdd(month,1,Convert(date,@StartDate)),
DateAdd(month, 2, Convert(date,@StartDate)),DateAdd(month, 3, Convert(date,@StartDate)),
DateAdd(month, 4, Convert(date,@StartDate)),DateAdd(month, 5, Convert(date,@StartDate)),
DateAdd(month, 6, Convert(date,@StartDate)),DateAdd(month, 7, Convert(date,@StartDate)),
DateAdd(month, 8, Convert(date,@StartDate)),DateAdd(month, 9, Convert(date,@StartDate)),
DateAdd(month, 10, Convert(date,@StartDate)),DateAdd(month, 11, Convert(date,@StartDate))
from
(select Mth, MonthlyBalance from #SG_MonthlyTotals) as src
PIVOT
(
sum(MonthlyBalance)
For Mth
IN
(
[Convert(date,@StartDate)],[DateAdd(month, 1, Convert(date,@StartDate))],
[DateAdd(month, 2, Convert(date,@StartDate))],[DateAdd(month, 3, Convert(date,@StartDate))],
[DateAdd(month, 4, Convert(date,@StartDate))],[DateAdd(month, 5, Convert(date,@StartDate))],
[DateAdd(month, 6, Convert(date,@StartDate))],[DateAdd(month, 7, Convert(date,@StartDate))],
[DateAdd(month, 8, Convert(date,@StartDate))],[DateAdd(month, 9, Convert(date,@StartDate))],
[DateAdd(month, 10, Convert(date,@StartDate))],[DateAdd(month, 11, Convert(date,@StartDate))]
)
)
as PVT

2

The error indicate the date passed to the sp is nvarchar (not datetime). Make sure the passed value is a valid date (if using other than date or datetime, format should be YYYY-MM-DD).

Another thing - when you insert to #SG_MonthlyTotals you use "select a.*,......". If field is added to the table (in the future) your sql will break. It is recommended that you specify each field.

3

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
Go
alter procedure [dbo].[SG_FinancialReports_Budgets_YTD]
@StartDate Datetime,
@EndDate Datetime,
@BudgetID varchar(3)
as

It's definitely DateTime and the passed values in the Pivot section are even Convert to Date. The sp works with this. I only added the Pivot to the end after it was working. This is the only change.

4

Try this:

select @startdate as startdate
      ,[January]
      ,[February]
      ,[March]
      ,[April]
      ,[May]
      ,[June]
      ,[July]
      ,[August]
      ,[September]
      ,[October]
      ,[November]
      ,[December]
  from (select datename(month,mth) as mth
              ,monthlybalance
          from #sg_monthlytotals
       ) as a
 pivot (sum(monthlybalance)
   for mth in ([January]
              ,[February]
              ,[March]
              ,[April]
              ,[May]
              ,[June]
              ,[July]
              ,[August]
              ,[September]
              ,[October]
              ,[November]
              ,[December]
           )
       ) as b
;

Ps.: Were you trying to make columnnames dynamic? If so, dynamic queries are not recommended (but it can be done).

5

After adding this to my existing sp then running it I'm only getting a single result. I need to get all the data for all the fields from the temp table and then have the MontlyBalance sum for each month across.

StartDate January February March April May June July August September October November December

2015-11-01 00:00:00.000 0.00 0.00 0.00 0.00 0.00 NULL NULL NULL NULL NULL 0.00 0.00

6

The sp brings in financial data for each month from @StartDate to @EndDate. I need all the Mth in that dataset to pivot with the months across.

This is how my sp works without the pivot. I need all fields to repeat then the MonthlyBalance amounts to appear in the Mth columns across. The Mth gets updated from the parameters. It's never January to December but I guess since the parameters restrict the months anyway I can input all the months in.

Pasted image2938×436 37.6 KB

7

I've been able to get the data and dates across with this code, however, the data doesn't appear to be correct. Nulls are appearing where there is actual data and the PY column is completely blank even though there are values. I'll keep chugging away at it.

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(Mth)
FROM #SG_MonthlyTotals
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT GLAcct,
GLDescription,
AcctType,
MainAccount,
Location,
Division,
ContractType,
Category,
SubCategory,
MonthlyBudget,
MonthlyBalancePY, ' + @cols + ' from
(
select GLAcct,
GLDescription,
AcctType,
MainAccount,
Location,
Division,
ContractType,
Category,
SubCategory,
MonthlyBudget,
MonthlyBalancePY,
Mth,
MonthlyBalance
from #SG_MonthlyTotals
) x
pivot
(
min(MonthlyBalance)
for Mth in (' + @cols + ')
) p '

execute(@query)