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 ,  AS InvestigationMethod1 --THIS HAS NO LENGTH ,  AS InvestigationMethod2 --THIS HAS NO LENGTH ,  AS InvestigationMethod3 --THIS HAS NO LENGTH ,  AS InvestigationMethod4 --THIS HAS NO LENGTH ,  AS InvestigationMethod5 --THIS HAS NO LENGTH , CASE WHEN  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 (, , , , , )) 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: , , , 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.