Multiple select from CTE with different number of rows in a StoredProcedure

Question

How to do two select with joins from the cte's which returns total number of columns in the two selects?

I tried doing union but that appends to the same list and there is no way to differentiate for further use.

WITH campus AS
(SELECT DISTINCT CampusName, DistrictName
FROM            dbo.file
),creditAcceptance AS
(SELECT        CampusName, EligibilityStatusFinal, CollegeCreditAcceptedFinal, COUNT(id) AS N
FROM            dbo.file
WHERE        (EligibilityStatusFinal LIKE 'Eligible%') AND (CollegeCreditEarnedFinal = 'Yes') AND (CollegeCreditAcceptedFinal = 'Yes')
GROUP BY CampusName, EligibilityStatusFinal, CollegeCreditAcceptedFinal
),eligibility AS
(SELECT        CampusName, EligibilityStatusFinal, COUNT(id) AS N, CollegeCreditAcceptedFinal
FROM            dbo.file
WHERE        (EligibilityStatusFinal LIKE 'Eligible%') 
GROUP BY CampusName, EligibilityStatusFinal, CollegeCreditAcceptedFinal
)
SELECT        a.CampusName, c.[EligibilityStatusFinal], SUM(c.N) AS creditacceptCount
FROM            campus  as a FULL OUTER JOIN creditAcceptance as c ON a.CampusName=c.CampusName
WHERE        (a.DistrictName = 'xy')
group by a.CampusName ,c.EligibilityStatusFinal
Union ALL
SELECT      a.CampusName ,  b.[EligibilityStatusFinal], SUM(b.N) AS eligible
    From Campus as a FULL OUTER JOIN eligibility    as b ON a.CampusName = b.CampusName

WHERE        (a.DistrictName = 'xy') 
group by a.CampusName,b.EligibilityStatusFinal

Expected output:

+------------+------------------------+--------------------+
| CampusName | EligibilityStatusFinal | creditacceptCount  |
+------------+------------------------+--------------------+
| M          | G                      | 1                  |
| E          | NULL                   | NULL               |
| A          | G                      | 4                  |
| B          | G                      | 8                  |
+------------+------------------------+--------------------+

+------------+------------------------+----------+
| CampusName | EligibilityStatusFinal | eligible |
+------------+------------------------+----------+
| A          | G                      |        8 |
| C          | G                      |        9 |
| A          | T                      |        9 |
+------------+------------------------+----------+

score 0 · Answer 1 · answered Apr 24 '19 at 09:32

As you can see here CTEs can be used in a single statement only, so you can't get the expected output with CTEs.

Here is an excerpt from Microsoft docs:

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.

You can use table variables (declare @campus table(...)) or temp tables (create table #campus (...)) instead.

Multiple select from CTE with different number of rows in a StoredProcedure

1 Answers1