February 21, 2018 at 10:24 am
Hello forum, I am new to posting as I try to read a lot to address problems i have had in the past. I have searched the forum but cant seem to find anything I need to put me on the right path.
I have done the dynamic pivot query and it works. What I want to do is to rename the pivot columns as well as they are dates and would like to make them generic for the purpose of a ssrs report. Example, instead of 201802, 201801 etc as the columns, also change them to n1, n2 etc. I thought I could do that with column aliases, but its not working in my dynamic query.
I want to do something along the lines of :
Declare @SQLPivotQuery as varchar(max)
Declare @PeriodPivot as varchar(max)
Declare @GenericPeriodPivot as varchar(max)
-----------------------------------------------------------------------------------------------
Select @PeriodPivot = Coalesce(@PeriodPivot + ', ','') + QUOTENAME(Fund_Mth)
FROM ##temp_aScvbvb1 as mrapivot
Order by fund_mth
Select @PeriodPivot
------------------------------------------------------------------------------------------------
Select @GenericPeriodPivot = Coalesce(@GenericPeriodPivot + ', ','') + quotename(genericperiod)
FROM ##temp_xcxcv
select @GenericPeriodPivot
Set @SQLPivotQuery =
'Select
mbr_id
,[carrier]
,[Market]
,[last_name]
,[first_name]
,[middle_name]
,[gender]
,[birth_date]
,[deceased]
,[deceased_date],' + @PeriodPivot + ' as ' + @GenericPeriodPivot + '
FROM
(
Select mbr_id, ,[carrier],[Market],[last_name],[first_name],[middle_name],[gender],[birth_date]
,[deceased],[deceased_date]' + @PeriodPivot + '
From table zzzz) as src
Pivot Max(ma_raf)
For fund_mth in ( ' + @PeriodPivot + ')) as pvt)
I get errors for the aliases that I am trying to assign with the genericperiodpivot.
I hope I made sense.
February 21, 2018 at 12:02 pm
IF you do a PRINT @SQLPivotQuery
, you will most likely get something like this,[deceased_date], [201801], [201802] as [n1], [n2]
where, in fact, you are looking for something like this,[deceased_date], [201801] AS [n1], [201802] AS [n2]
You need to join your temp tables to get the relationship between [Fund_Mth] and [genericperiod]
Then you need to replace thisSelect @GenericPeriodPivot = Coalesce(@GenericPeriodPivot + ', ','') + quotename(genericperiod)
with something like thisSelect @GenericPeriodPivot = Coalesce(@GenericPeriodPivot + ', ','') + QUOTENAME(Fund_Mth) + ' AS ' + quotename(genericperiod)
Now in your PIVOT statement you need to replace this,[deceased_date],' + @PeriodPivot + ' as ' + @GenericPeriodPivot + '
with something like this,[deceased_date],' + @GenericPeriodPivot + '
February 21, 2018 at 1:50 pm
Got it. Thank you. I worked though it and got it running earlier today.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply