Multiple Select Statements in a View

I would like to create a view in MS SQL Server to return the results of multiple select statements.  When I tried to create the view only the first select statement was included.  I am trying to make it so that the view can be returned in an Excel spreadsheet.  I tried Union All and that did not work.  Can anyone recommend a solution?
mounty95Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
You can always fire multiple queries from Excel?
Or add a column which you can use to filter.  Copy once to Excel, then break up using VBA...

select * from
(select dataset=1, col1, col2... from ..
 union all
 select dataset=2, col1, col2... from ..) sq
0
 
cyberkiwiCommented:
A view can only return a single result set. A stored proc can return multiple, but it can be tricky to access them all from your programming environment.

A single view returns data in a specific number of columns - so if your query 1 returns
int,int,varchar
and the 2nd intended result is
money,varchar,datetime
that won't gel even using UNION all.

Either fire 2 queries from the front-end, or convolutedly merge the data by lining up the separate result set columns and casting to a type that will accept both results -- very bad idea
0
 
mounty95Author Commented:
Here are the first 2 queries which other than the column that the evaluations are being made on are identical.

Select FY as Roadways
     , Superior = IsNull(sum(case when roadways ='Superior' then 1 end),0)
     , SuperiorPercentage = (IsNull(sum(case when roadways ='Superior' then 1.0 end),0.0) / count(*))*100
     , Good =IsNull(sum(case when roadways ='Good' then 1 end),0)
     , GoodPercentage = (IsNull(sum(case when roadways ='Good' then 1.0 end),0.0) / count(*))*100
     , SuperiorGood = ((IsNull(sum(case when roadways ='Superior' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when roadways ='Good' then 1.0 end),0.0) / count(*))*100)
     , Adequate = IsNull(sum(case when roadways ='Adequate' then 1 end),0)
     , AdequatePercentage = (IsNull(sum(case when roadways ='Adequate' then 1.0 end),0.0) / count(*))*100
     ,SuperiorGoodAdequate=((IsNull(sum(case when roadways ='Superior' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when roadways ='Good' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when roadways ='Adequate' then 1.0 end),0.0) / count(*))*100)
     , NotAdequate = IsNull(sum(case when roadways ='Not Adequate' then 1 end),0)
     , NotAdequatePercentage = (IsNull(sum(case when roadways ='Not Adequate' then 1.0 end),0.0) / count(*))*100
     , Poor = IsNull(sum(case when roadways ='Poor' then 1 end),0)
     , PoorPercentage = (IsNull(sum(case when roadways ='Poor' then 1.0 end),0.0) / count(*))*100
     , NotAdequatePoor=((IsNull(sum(case when roadways ='Not Adequate' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when roadways ='Poor' then 1.0 end),0.0) / count(*))*100)
     ,count(*) as [Total Inspections]
FROM maintenance_survey_results
where reinspection<>'1' or reinspection is null
GROUP BY FY
ORDER BY FY

Select FY as site_appearance
     , Superior = IsNull(sum(case when site_appearance ='Superior' then 1 end),0)
     , SuperiorPercentage = (IsNull(sum(case when site_appearance ='Superior' then 1.0 end),0.0) / count(*))*100
     , Good =IsNull(sum(case when site_appearance ='Good' then 1 end),0)
     , GoodPercentage = (IsNull(sum(case when site_appearance ='Good' then 1.0 end),0.0) / count(*))*100
     , SuperiorGood = ((IsNull(sum(case when site_appearance ='Superior' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when site_appearance ='Good' then 1.0 end),0.0) / count(*))*100)
     , Adequate = IsNull(sum(case when site_appearance ='Adequate' then 1 end),0)
     , AdequatePercentage = (IsNull(sum(case when site_appearance ='Adequate' then 1.0 end),0.0) / count(*))*100
     ,SuperiorGoodAdequate=((IsNull(sum(case when site_appearance ='Superior' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when site_appearance ='Good' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when site_appearance ='Adequate' then 1.0 end),0.0) / count(*))*100)
     , NotAdequate = IsNull(sum(case when site_appearance ='Not Adequate' then 1 end),0)
     , NotAdequatePercentage = (IsNull(sum(case when site_appearance ='Not Adequate' then 1.0 end),0.0) / count(*))*100
     , Poor = IsNull(sum(case when site_appearance ='Poor' then 1 end),0)
     , PoorPercentage = (IsNull(sum(case when site_appearance ='Poor' then 1.0 end),0.0) / count(*))*100
     , NotAdequatePoor=((IsNull(sum(case when site_appearance ='Not Adequate' then 1.0 end),0.0) / count(*))*100)+((IsNull(sum(case when site_appearance ='Poor' then 1.0 end),0.0) / count(*))*100)
     ,count(*) as [Total Inspections]
FROM maintenance_survey_results
where reinspection<>'1' or reinspection is null
GROUP BY FY
ORDER BY FY

I am pulling these into Excel for presentation purposes only.  I have Crystal Reports available as well, but I can't see to get this to work properly there so I figured a simple pulling in of the data with all of the columns that I needed would work.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
since they do line up, drop the first ORDER BY in exchange for UNION ALL
you cannot have two ORDER BY's in the same query.
0
 
mounty95Author Commented:
Not quite what I was looking for.  I want the select statements to return as individual grids for each select.  I didn't realize that Union was going to literally join the results...guess that is common sense.  Any other ideas?
0
 
cyberkiwiCommented:
As noted above, a view cannot return 2 results.
Much as a Table cannot store 2 sets of data... (you could store 1 set logically broken by a column, but you get what I mean)
It is just not possible.
0
 
mounty95Author Commented:
Any suggestions?  Have you ever seen this need?  I understand what you mean, but there must be some way to take the individual grids that I have and somehow copy them into Excel or maybe not
0
 
mounty95Author Commented:
Thanks for the attention to this question.  Unfortunately I don't see any easy way of handling 35 queries.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.