INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dynamic pivot returns PIVOT operator error

Dynamic pivot returns PIVOT operator error

(OP)
I am trying to construct a dynamic pivot query for a report that uses a company period counts table for monthly membership by chapter. What the table has a column for the year and a separate column for the month as there is a stored procedure that fires monthly to populate the values for the previous month. What I was looking for was a way to have the query dynamically put the results into a report, adding the next months column when it was present. The months are simple numbers 1,2,3,4,5 ect. I am getting this error "Msg 207, Level 16, State 1, Line 4
Invalid column name '1'." for each month number and then "Msg 265, Level 16, State 1, Line 4
The column name "1" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument."

The query is

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @col AS VARCHAR(MAX)
DECLARE @start_date datetime --first day of last month
DECLARE @finish_date datetime --last day of last month
DECLARE @this_month datetime --current day of last month
SET @this_month = DATEADD(mm,-1,GetDate())
SET @start_date = CONVERT(datetime,STR(MONTH(@this_month))+'/01/'+
STR(YEAR(@this_month)),101)
IF MONTH(@this_month)<12
SET @finish_date = DATEADD(day,-1,CONVERT(datetime,STR(MONTH(@this_month)+1,2)
+'/01/'+STR(YEAR(@this_month))+ ' 23:59:00',101))
ELSE
SET @finish_date=DATEADD(day,-1,convert(datetime,'01/01/'+STR(YEAR(@this_month)+1)+ ' 23:59:00',101))

select @col = STUFF((SELECT ',' + QUOTENAME(h10_month)
from (select distinct h10_month from client_company_period_counts
where h10_year = datepart(yyyy,GETDATE())) as h10_month

group by h10_month
order by h10_month
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

SET @sql = N'


SELECT * from(
SELECT h10_chp_code,
h10_chp_name,
h04_division_code,
chs_code,
h10_calculated_balance,
' +@col+ '
from client_company_period_counts pc
join co_chapter cp(nolock) on cp.chp_cst_key = pc.h10_chp_cst_key
join co_chapter_ext cx on cp.chp_cst_key=cx.chp_cst_key_ext
join client_company_division d on cx.chp_h04_key_ext = d.h04_key
join co_chapter_status cs (nolock)on cs.chs_key = cp.chp_chs_key

WHERE DATEPART(yyyy,' + convert(nvarchar,@finish_date,101) + ') = h10_year
AND chp_cht_key not in (''08A654F6-9A0A-41A2-B081-1A31BB1BE45A''
,''F3970CE2-9AF4-4761-81D3-05B9E5D43853'')
AND chp_asn_key = ''DEA92516-84C1-4E65-937B-F45349BD3159''
AND chp_terminate_date is null
AND h10_delete_flag = 0
)SRC
PIVOT
(Sum(h10_calculated_balance)
FOR h10_month in ('+@col+'))AS PVT';

CODE --> SQL

 


execute sp_executesql @sql;

Any help is greatly appreciated.

RE: Dynamic pivot returns PIVOT operator error

(OP)
I have found the issue. WHERE DATEPART(yyyy,' + convert(nvarchar,@finish_date,101) + ') = h10_year was throwing everything off..

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close