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

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