t为什么这个sql select语句会丢失varchar长度，以及如何重新指定它

Question

我有一个select语句，它使用pivot聚合一些数据，在一行中列出一个ID的多行数据。现在，尽管我遇到的问题并不是让任何东西摔倒或失败，但我想知道为什么要旋转的varchar（75）字段的长度会丢失varchar长度。这是我的问题：

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

请查看我在查询中的注释，指定有问题的确切字段。注释"--THIS OF DATA TYPE varchar（75）"旁边的字段的长度为75，正如我所说，但当它被旋转时，上面的字段：[1]、[2]、[3]等。它们会丢失长度，并显示为没有特定长度的varchar。为什么会发生这种情况，以及如何为这些新字段指定长度？请帮忙，他们在我的SSIS包中抛出警告，我很想解决这个问题。

Answer 1

不知道你为什么会看到这种行为，但我有一个解决方法：

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

只需使用显式Cast()强制您想要的数据类型！