I've a requirement in which I need to display the column values in a sinlge row. Please let me know how can I write the query so that I can convert the multiple columns to a single row.
I must be confused because your column values will all be in one row already! If I have a table that looks like this:
TableA: Col1 Col2 Col3 ---- ---- ---- 1 2 3 a b cThen doing a SELECT on that table will return column values from Col1, Col2, and Col3 in one row.
If you mean to return them in one column, then you will have to concatenate the columns together. For instance, the following query:
SELECT Col1||' '||Col2||' '||Col3 AS results FROM TableA;will return the following results:
RESULTS ------------------ 1 2 3 a b cIf you wish to display each column value on a separate line, then you will have to issue a query similar to the following:
SELECT Col1 AS results FROM TableA UNION SELECT Col2 AS results FROM TableA UNION SELECT Col3 AS results FROM TableA;The results will be something like:
RESULTS -------- 1 a 2 b 3 cIf this did not answer your specific question, please get back to me. Give me an example of what you are trying to accomplish.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.