Using UNION to make a header
I'm trying to write a SQL statement where the result would look like the one below.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
student_id start_date 123 2000 234 2002
where 'student_id' and 'start_date' are literals selected from dual:
SELECT 'student_id' , 'start_date' FROM dual UNION SELECT student_id , start_dt FROM student_table ORDER BY 1; The result: 123 2000 234 2002 student_id start_date
So how do I write my statement so that 'student_id' and 'start_date' are on the top row?
Your two-part UNION is creating a two-part output
- A one-line header
- A multi-line body
You need to sort by two criteria:
- The header line comes before the body lines
- Within each section, lines are sorted by student_id (Since there's only one line in the header section, this will only really apply to the body.)
Now you need some way of telling SQL whether a line is in the header or body, so it can sort correctly. Here's one way:
SELECT 'student_id' , 'start_date' , 1 -- Header will be sorted 1st FROM dual UNION SELECT student_id , start_dt , 2 -- Body will be sorted 2nd FROM student_table ORDER BY 3, 1;
This produces the following results:
student_id start_date 1 123 2000 2 234 2002 2
If you make an in-line view out of this result set, you will have the last column available for sorting without having to display it.
SELECT student_id , start_dt FROM ( SELECT 'student_id' AS student_id , 'start_date' AS start_dt , 1 AS section_num FROM dual UNION SELECT student_id , start_dt , 2 AS section_num FROM student_table ) ORDER BY section_num , student_id;
This is a simple example of a master-detail query. For a more complicated example using the same technique, see the last part of Rudy Limeback's column from May 14, 2003.