×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Dynamic pivot returns PIVOT operator error

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!

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