My table holds information on each employee. For Example:
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
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.
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.