CTE SQL Transact How to get the right Output displayed?-CodePudding

I hope someone can help me. I am trying to get the columns stadt, größtes, mittleres and kleines to display for the cities München and Stuttgart but I am getting all the columns displayed horizontally . München and Stuttgart should be on 2 different rows. Inner join, left join don't seem to work hence I just listed the display result as select * from cte, cte1. How can I get the proper display I want?

with cte as
(
select  left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
where stadt ='Stuttgart'
group by stadt
),
cte1 as
(
select  left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
where stadt ='München'
group by stadt
)
select * from cte, cte1

CodePudding user response：

This is a simple query that would meet your requirements.

select  left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
where stadt in ('Stuttgart','München')
group by stadt

if you have to have CTE, you an try something like this

with cte as 
(select  left(ab.stadt,3) as stadt, avg(gehalt) as mittleres, max(gehalt) as größtes, min(gehalt) as kleinstes
from gehalt g
inner join mitarbeiter m
on m.m_nr=g.m_nr
inner join abteilung ab
on ab.abt_nr=m.abt_nr
group by stadt
)
select * from cte where stadt in ('Stuttgart','München')

Although I would recommend the former query, there is no reason to have a CTE.