Solved

SQL - order result set when using union

Posted on 2016-08-24
2
41 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
0
Comment
Question by:jagr12
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41769238
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.
0
 

Author Comment

by:jagr12
ID: 41769284
Jim,
Thank you very much. This is exactly what I need.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question