Dynamic Pivot Help

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

  • 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 this
    Select @GenericPeriodPivot = Coalesce(@GenericPeriodPivot + ', ','') + quotename(genericperiod)
    with something like this
    Select @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 + '

  • 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