I have three select statements which have the bulk of their columns which are the same except a few. So here is an example
select
*orgid='0'
,'P' as OrgType*
, Lang
,*cast(Null as varchar) as BoardName
,cast(Null as varchar) as SchoolName*
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3
FROM *TABLEA*
WHERE *[Include_All] = 1*
Group By *[Lang]*
select
orgid=boardname
,'B' as OrgType
, Lang
, BoardName
,cast(Null as varchar) as SchoolName
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3
FROM TABLEB
WHERE [Include_Board] = 1
Group By [Lang], BoardName
select
orgid=SchoolName
,'S' as OrgType
, Lang
, BoardName
,SchoolName
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(5) THEN 1 ELSE 0 END) as Column1
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(6) THEN 1 ELSE 0 END) as Column2
,SUM(CASE WHEN IncludeColumn = 1 AND SecondInclude IN(4) THEN 1 ELSE 0 END) as Column3
FROM TABLEC
WHERE [Include_School] = 1
Group By [Lang], BoardName
So as you can see, the calculated columns are the same in each "grouping". The problem is that I have about 70 calculated fields (which are the same) in all three select statements. This makes reading the script horrible as i have to endlessly scroll.
The main changes are the first couple of columns in the select statement and then the way it groups by at the end.
Right now my current solution is to set strings and execute them. IE, I'll have
header1 = "SELECT FROM all..."
header2 = "SELECT FROM board.."
header3 = "SELECT FROM school.."
footer1 = "GROUP BY lang"
footer2 = "GROUP BY lang, board"
footer3 = "GROUP BY lang, school"
Common = CalculatedFields
EXECUTE (header1 + common + footer1)
EXECUTE (header2 + common + footer2)
but this just makes my script all red because its one large string. Not only that I have to break it up into different strings because I have a lot of text.
Any ideas on how to best do this while maintaining readability and clarity?
Best Answer
Use a CROSS APPLY (or perhaps OUTER APPLY) to replace the case statement in your aggregates like this:
where now the table f can (as appropriate) be:
This has the additional advantage of being data-driven; in the case that your masking needs to be amended, only a data change is required instead of a code change.