Example:
| Col_id | Col_Name |
|---|---|
| 1 | Apples |
| 2 | Oranges |
| 3 | Grapes |
| 4 | Pineapple |
SQL Query:
SELECT RTRIM(XMLAGG(XMLELEMENT(e,a.col_Name
|| '; ')).EXTRACT('//text()'),'; ') new_column
FROM TABLE_NAME a;
Output:
| New_column |
|---|
| Apples,Oranges,Grapes,Pineapple |
Home » Unlabelled
Thursday, 31 July 2014
Converting rows to column in the Oracle
While writing a SQL query, sometimes you often want to convert the rows to column. This will be hellpful when you are joining with the another table and you don't want the result to be dublicated per row.
Example:
SQL Query:
SELECT RTRIM(XMLAGG(XMLELEMENT(e,a.col_Name
|| '; ')).EXTRACT('//text()'),'; ') new_column
FROM TABLE_NAME a;
Output:
Example:
SQL Query:
SELECT RTRIM(XMLAGG(XMLELEMENT(e,a.col_Name
|| '; ')).EXTRACT('//text()'),'; ') new_column
FROM TABLE_NAME a;
Output:
0 comments:
Post a Comment