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

Multiple rows from multiple columns

My table holds information on each employee. For Example:

employee

cashtip
salestip
chargetip
venue
businessdate

I need to create a file that has one row for each of the categories. For example:

employee venue cashtip   businessdate

employee venue salestip businessdate
employee venue chargetip businessdate

Any suggestions?


Use UNION ALL. In case you also want to track which data column the resulting data came from, it's a good idea to add a "category type" column as well:

select employee 
     , venue 
     , 'charge' as category
     , chargetip   
     , businessdate
  from yourtable
union all
select employee 
     , venue 
     , 'sales' 
     , salestip   
     , businessdate
  from yourtable
union all
select employee 
     , venue 
     , 'cash' 
     , cashtip   
     , businessdate
  from yourtable

Notice how the CATEGORY column has the string 'charge' in the first query in the union. This is because the column name, datatype and length are taken from the first query in the union. Always put the longest string first, so that you don't have to pad a shorter one with blanks. Otherwise, if you start with a shorter one, the longer ones might get cut off.


This was last published in February 2005

Dig Deeper on Oracle database design and architecture

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close