Search code examples
sqlt-sqlssispivotvarchar

Why does this sql select statement lose the varchar length and how do I re-specify it?

I have a select statement that aggregates some data using a pivot to list multiple rows of data for one ID in one row going across. Now although the issue I'm coming across isn't making anything fall over or fail, I'd like to know why the length of the varchar(75) field(s) being pivoted are losing the varchar length. Here's my query:

SELECT MainID
 , [1] AS InvestigationMethod1    --THIS HAS NO LENGTH
 , [2] AS InvestigationMethod2    --THIS HAS NO LENGTH
 , [3] AS InvestigationMethod3    --THIS HAS NO LENGTH
 , [4] AS InvestigationMethod4    --THIS HAS NO LENGTH
 , [5] AS InvestigationMethod5    --THIS HAS NO LENGTH
 , CASE
     WHEN [6] IS NOT NULL THEN
       'True'
     ELSE
       'False'
   END AS InvestigationFlag
FROM
  (SELECT MainID
    , row_number() OVER (PARTITION BY MainID ORDER BY MainID, GeneralInvestigationMethod.LookupInvestigationMethodID) AS 'RowNumber'
    , InvestigationMethods    --THIS IS OF DATA TYPE varchar(75)
FROM
 GeneralInvestigationMethod
 LEFT OUTER JOIN LookupInvestigationMethod
   ON GeneralInvestigationMethod.LookupInvestigationMethodID = LookupInvestigationMethod.LookupInvestigationMethodID) AS InvestigationMethodSource
PIVOT (max(InvestigationMethodName) FOR [RowNumber] IN ([1], [2], [3], [4], [5], [6])) AS InvestigationMethodPivot

Please see my comments within the query specifying the exact fields in question. The field next to the comment "--THIS IS OF DATA TYPE varchar(75)" has a length of 75 like I said, however when it is pivoted the the above fields: [1], [2], [3], etc. they lose their length and display as just a varchar without a specific length. Why does this happen and how can I specify a length for these new fields? Please help, they're throwing warnings in my SSIS package and I would love to solve this issue.

Solution

  • Not sure why you're seeing this behaviour, but I do have a workaround for you:

    SELECT MainID
     , Cast([1] As varchar(75)) As InvestigationMethod1
     , Cast([2] As varchar(75)) As InvestigationMethod2
     , Cast([3] As varchar(75)) As InvestigationMethod3
     , Cast([4] As varchar(75)) As InvestigationMethod4
     , Cast([5] As varchar(75)) As InvestigationMethod5
...

    Just force the data type you want using an explicit Cast()!