Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using UNION to make a header

I'm trying to write a SQL statement where the result would look like the one below.

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

  1. A one-line header
  2. A multi-line body

You need to sort by two criteria:

  1. The header line comes before the body lines
  2. 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.

This was last published in November 2003

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close