Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

Solved

SQL - order result set when using union

Posted on 2016-08-24

1 solution

Medium Priority

61 Views

Last Modified: 2016-08-24

When using union all, how can I order result set of how it gets displayed? For example

Right now it returned

Descripton SalesType Customercode Jan      Feb       Mar       Apr      May      Jun      Jul      Aug      Oct      Nov      Dec      Total
Sale        Direct        1        $ $ $
Manager        Resell        1        $ $ $
Inter        Extr        1        $ $ $
Exter        EU        1        $ $ $

I want to return like or any order of result set

Descripton SalesType Customercode Jan      Feb       Mar       Apr      May      Jun      Jul      Aug      Oct      Nov      Dec      Total

Inter        Extr        1        $ $ $
Exter        EU        1        $ $ $
Manager        Resell        1        $ $ $
Sale        Direct        1        $ $ $

Table sample

column names

Descripton SalesType Customercode Jan      Feb       Mar       Apr      May      Jun      Jul      Aug      Oct      Nov      Dec      Total

select *from
(
select * from #temp_table1
union all
select * from #temp_table2
union all
select * from #temp_table3
union all
select * from #temp_table4

a group by Descripton,SalesType,Customercode,Jan,Feb ,Mar Apr,May,Jun,Jul,Aug,Oct,Nov,Dec,Total

Comment

Question by:jagr12

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 66

Accepted Solution

by:Jim Horn

Jim Horn earned 2000 total points

ID: 417692382016-08-24

To pull off a customized ordering you'll need to include an ORDER BY with a CASE block, like this..
<total air code, look at the last line>

select * from (
   select * from #temp_table1
   union all 
   select * from #temp_table2
   union all 
   select * from #temp_table3
   union all 
   select * from #temp_table4) a
group by Descripton,SalesType,Customercode,Jan,Feb ,Mar Apr,May,Jun,Jul,Aug,Oct,Nov,Dec,Total
ORDER BY CASE Descripton 
   WHEN 'Inter' THEN 1 
   WHEN 'Exter' THEN 2 
   WHEN 'Manager' THEN 3 
   WHEN 'Sale' THEN 4 
   ELSE 5 END

Open in new window

For a tutorial on CASE blocks check out SQL Server CASE Solutions.

Author Comment

by:jagr12

ID: 417692842016-08-24

Jim,
Thank you very much. This is exactly what I need.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Add Theme to ASP.NET MVC

Article by: Shaun

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project

Export Table Data in SQL Server Management Studio

Article by: Dirk

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.

Executing a SQL Server Function from Within Access

Video by: TechMommy

Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…