title: Add case Statement to large query to allow result set to be more dynamic
body: <p>I have the following MS SQL query using SSMS 2012. I've been fine tuning and tweaking this for a while. You can learn more about it here.</p>
  
  <p><a href="https://stackoverflow.com/questions/21069783/case-statement-in-monster-query">CASE statement in monster query</a></p>
  
  <pre><code>WITH Data AS
  (   SELECT b.ReportHeading1, 
              p.DisplayOrder, 
              p.MemberCode,
              m.PortfolioGroupCode as DistGroup,
              m.PortfolioGroupCode as DistCode,
              m.MemberCode as Packgroup,
              g.purpose,
              gg.purpose as purpose2,
              c.DeliveryName,
              p.PortfolioGroupID as pid,
              m.PortfolioGroupID as mid,
              convert(varchar(max),lb.value) as repset,
                          RowNumber = ROW_NUMBER() OVER(PARTITION BY m.portfoliogroupid, m.membercode ORDER BY p.DisplayOrder)
      FROM    [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] p
                  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBase] b
                  ON b.PortfolioBaseID = p.PortfolioGroupID
                  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroupMember] m
                   on m.MemberID = p.PortfolioGroupID
                  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] g
                  ON g.PortfolioGroupID = m.PortfolioGroupID
                     LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] gg
                  ON m.MemberCode = gg.PortfolioGroupCode
                  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseLabels] lb
                  on p.MemberID = lb.PortfolioBaseID
                  LEFT OUTER JOIN [APXFirm].[AdvApp].[vContact] c
                  on c.ContactCode = g.Purpose
                  WHERE     m.PortfolioGroupCode like '%_Dist%'
                  and (g.Purpose like '%_ind' or g.Purpose like '%group')
                  and lb.Label = '$repset'
                  --and m.membercode like '%pack%'
                         )
  SELECT
  t.ContentSetName,
  '' as 'DistributionDesc',
  CASE    WHEN ISNULL(d.purpose2,'.') &lt;&gt; ISNULL(d.purpose,'.')
      THEN CAST(d.ReportHeading1+'_ - '+d.DeliveryName AS NVARCHAR(MAX))
      ELSE d.ReportHeading1+'_' END AS DistributionCustom,
      CONCAT(d.mid,'.',d.pid,'_',d.DisplayOrder,'_', 
  
      CASE 
      WHEN t.ContentSetName LIKE 'Cover%' THEN 'Cover'
      WHEN t.ContentSetName = 'Separator_docx' THEN 'Separator'
      WHEN t.ContentSetName LIKE '%Report%' THEN 'Report'
      END
      ,'_',d.purpose) as DistributionName,
              'False' as IsForFunctionalGroup,
      'True' as IsLandscapePageNum,
      1 as NumOfCopies,
      d.purpose as RecipientCode,
      d.ReportHeading1 as RecipientFullName,
      d.MemberCode as ReportingEntityCode,
      'Quarterly' as RunEvent
  
      FROM    Data d
          CROSS APPLY 
          (   VALUES
                  ('Cover_SSRS'),
                  ('Separator_docx'),
                   (d.repset) 
                          ) t (ContentSetName)
  WHERE   d.RowNumber = 1
  OR      t.ContentSetNAme != 'Cover_SSRS'
  </code></pre>
  
  <p>This is the result set it produces..</p>
  
  <p><img src="https://i.stack.imgur.com/pow9x.jpg" alt="table"></p>
  
  <p>As you may note in the content set <strong>Cover_SSRS</strong> is hard coded in the CROSS APPLY portion of the query. However I would like a CASE statement or some type of boolean logic that references the Contact View used in the query</p>
  
  <p>[APXFirm].[AdvApp].[vContact]</p>
  
  <p><img src="https://i.stack.imgur.com/9sJ5k.jpg" alt="Contact View"></p>
  
  <p>In the contact view noted above is the column <strong>Email3</strong>. If the value of Email3 is yes@yes.com I would like instead of Cover_SSRS in the contentsetname column for <strong>Cover_EmailSSRS</strong> to be alternatively used. Thus in the same result set if the value of Email3 was yes@yes.com the result set would look as follows:</p>
  
  <p><img src="https://i.stack.imgur.com/G7Yyq.jpg" alt="Result2"></p>
  
  <p>Thank you</p>