[Solved] Assig multiple columns in a case statement?

by


Your sample desired output isn’t all clear in my opinion, but maybe this is what you want?

SELECT 
  [FileName],
  CASE 
    WHEN [FileName] LIKE 'ATE_%' THEN CAST(SUBSTRING([FileName],5,5) AS NVARCHAR(100))
    WHEN [FileName] LIKE '%Adhoc%' THEN CAST([FileName] + ' ' + [SheetName] AS NVARCHAR(100))
    WHEN [FileName] LIKE 'AdvantageData%' THEN [SheetName] 
  END AS ABTALookUp,
  CASE 
    WHEN [FileName] LIKE 'ATE_%' THEN 'ABTA' 
    WHEN [FileName] LIKE '%Adhoc%' THEN 'FileSheet'
    WHEN [FileName] LIKE 'AdvantageData%' THEN 'Sheet' 
  END AS ABTASource

solved Assig multiple columns in a case statement?