Syntax error when using Convert inside PIVOT

Question

I need to convert my values to Varchar. I've got this code:

SELECT Value AS FieldName, [42] AS [KeyID_42],[600] AS [KeyID_600]
FROM
(
  SELECT TransID, Value, FieldName
  FROM 
  (
      SELECT TransID, [ErisaPlanEndsMM], [ErisaPlanEndsDD],
           [MLRAvgLivesNumber], [MLRAvgLivesRptYear]
      FROM tblSQLAdminInventory
  ) p
  UNPIVOT
   (FieldName FOR Value IN 
      (Convert(VarChar(250), [ErisaPlanEndsMM]) AS [ErisaPlanEndsMM],
       Convert(VarChar(250), [ErisaPlanEndsDD]) AS [ErisaPlanEndsDD],
       Convert(VarChar(250), [MLRAvgLivesNumber]) AS [MLRAvgLivesNumber],
       Convert(VarChar(250), [MLRAvgLivesRptYear]) AS [MLRAvgLivesRptYear])
   )AS unpvt
) AS SourceTable
PIVOT
(
MAX(FieldName)
FOR TransID IN ([42],[600])
) AS PivotTable

I'm getting the error:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Convert'.

All I've found online is cases where CAST was used, because the user needed to convert to Int. In my case, I want everything Varchar. Can anyone tell me the proper way to accomplish this?

I should add that this is a dynamic query and may contain several dozen fields, depending on the table. My code above is the SQL generated when run against one specific table.

Answer 1

The problem is that you are attempting to convert inside of your unpivot, that's not valid syntax. The data needs to be converted prior to attempting to unpivot.

You can fix this by putting your convert inside your subquery:

SELECT TransID, Value, FieldName
FROM 
(
    SELECT TransID, 
       Convert(VarChar(250), [ErisaPlanEndsMM]) AS [ErisaPlanEndsMM], 
       Convert(VarChar(250), [ErisaPlanEndsDD]) AS [ErisaPlanEndsDD],
       Convert(VarChar(250), [MLRAvgLivesNumber]) AS [MLRAvgLivesNumber], 
       Convert(VarChar(250), [MLRAvgLivesRptYear]) AS [MLRAvgLivesRptYear]
    FROM tblSQLAdminInventory
) p
UNPIVOT
(
    FieldName FOR Value IN 
      ([ErisaPlanEndsMM], [ErisaPlanEndsDD], [MLRAvgLivesNumber], [MLRAvgLivesRptYear])
)AS unpvt