Eg : #Oracle
Sample data :
| ID | A_ID | B_ID | C_ID | D_ID |
|---|---|---|---|---|
| 1 | 'ABC' | 'XYZ' | 'PLO' | 'QWE' |
Expected Output :
| ID | A_ID |
|---|---|
| 1 | 'ABC' |
| 1 | 'XYZ' |
| 1 | 'PLO' |
| 1 | 'QWE' |
CodePudding user response：
You can use the unpivot clause:
select id, a_id
from your_table
unpivot (a_id for orig_id in (a_id, b_id, c_id, d_id))
| ID | A_ID |
|---|---|
| 1 | ABC |
| 1 | XYZ |
| 1 | PLO |
| 1 | QWE |
The operation creates an extra column, which I've called orig_id, which can be used to identify which of the original columns a given value came from. I've excluded it from the select list, but the fiddle includes a version that shows it. You can also change the value that appears, by adding as '...' to each column name in the in () clause.
You can read more about pivoting and unpivoting in this article.