How To Transpose Columns To Rows?

simon87

Hi,

May someone support me ?

In this query,

SELECT camp.campo

      , sol.id_solicitud

      , monto_solicitud

  FROM solicitud sol

      , campos_documento cd

      , campos camp

WHERE sol.id_documento = cd.id_documento

AND cd.id_campo = camp.id_campo;

The result is this:

campo	id_solicitud	monto_solicitud
campo1	1	100
campo2	1	100

How could I move the fields in the following way?

campo	valor
campo1	1
campo2	100

simon87

Unpivot the table (pivoting would involve going from many rows and 2 columns to 1 row with many columns). You would most likely need to use dynamic SQL to generate the query and then use the DBMS_SQL package (or potentially EXECUTE IMMEDIATE) to execute it. You should also be able to construct a pipelined table function that did the unpivoting. You'd need to use dynamic SQL within the pipelined table function as well but it would potentially be less code. I'd expect a pure dynamic SQL statement using UNPIVOT to be more efficient, though.

An inefficient approach, but one that is relatively easy to follow, would be something like:

<span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> ed<br/>Wrote </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">file</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> afiedt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">buf<br/><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">create</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> replace type emp_unpivot_type<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">2</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> object </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">3</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  empno number</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  col varchar2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4000</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">5</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">);</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">/</span>

<span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">create</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> replace </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">function</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> unpivot_emp<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">2</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> p_empno </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">in</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> number </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">3</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">return</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> emp_unpivot_tbl<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  pipelined<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">5</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">is</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">6</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  l_val varchar2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4000</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">);</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">begin</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">8</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">for</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> cols </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">in</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> column_name </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> user_tab_columns </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> table_name </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'EMP'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">9</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  loop<br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">execute</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> immediate </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'select '</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> cols</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">column_name </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">' from emp where empno = :empno'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">11</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">into</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> l_val<br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">12</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">using</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> p_empno</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">13</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  pipe </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">row</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> emp_unpivot_type</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> p_empno</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> l_val </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">));</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">14</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> loop</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">15</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">return</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">16</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">end</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">/<br/><br/><span style="color: #242729; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 15px;">You can then call that in a SQL statement (I would think that you'd want at least a third column with the column name)<br/></span><br/><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">table</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> unpivot_emp</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">))</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">/</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/><br/>  EMPNO COL<br/></span><span class="com" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #858c93;">---------- ----------------------------------------</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> MILLER<br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> CLERK<br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7782</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">23</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">JAN-82<br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1301</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">10</span>

https://stackoverflow.com/questions/15100101/unpivot-on-an-indeterminate-number-of-columns?answertab=votes#tab-top

John Thorton

simon87 wrote:Hi,May someone support me ?In this query,SELECT camp.campo , sol.id_solicitud , monto_solicitud FROM solicitud sol , campos_documento cd , campos campWHERE sol.id_documento = cd.id_documentoAND cd.id_campo = camp.id_campo;The result is this:campoid_solicitudmonto_solicitudcampo11100campo21100How could I move the fields in the following way?campovalorcampo1campo2100

What happened to campo1?

How do I ask a question on the forums?

How do I convert rows to columns?

simon87

Hi,

Sorry,

It must be kept.

John Thorton

simon87 wrote:Hi, Sorry, It must be kept.

but it does NOT exist in what you showed as desired results

please READ & follow the 2 links I posted above

Etbin

Maybe

with

data as

(select 'campo1' campo,1 id_solicitud,100 monto_solicitud from dual union all 

select 'campo2',1,100 from dual

),

slicer(item1,item2,rest1,rest2) as

(select substr(cols1,1,instr(cols1,',') - 1),substr(cols2,1,instr(cols2,',') - 1),

        substr(cols1,instr(cols1,',') + 1)||',',substr(cols2,instr(cols2,',') + 1)||','

   from (select to_char(id_solicitud)||','||to_char(monto_solicitud) cols1,

                listagg(campo,',') within group (order by campo) cols2

           from data

          group by id_solicitud,monto_solicitud

        )

union all

select substr(rest1,1,instr(rest1,',') - 1),substr(rest2,1,instr(rest2,',') - 1),

        substr(rest1,instr(rest1,',') + 1),substr(rest2,instr(rest2,',') + 1)

   from slicer

where rest1 is not null

   and rest2 is not null

)

select item2 campo,item1 valor

  from slicer

order by item1

CAMPO	VALOR
campo1	1
campo2	100

Regards

Etbin

simon87

Unpivot the table (pivoting would involve going from many rows and 2 columns to 1 row with many columns). You would most likely need to use dynamic SQL to generate the query and then use the DBMS_SQL package (or potentially EXECUTE IMMEDIATE) to execute it. You should also be able to construct a pipelined table function that did the unpivoting. You'd need to use dynamic SQL within the pipelined table function as well but it would potentially be less code. I'd expect a pure dynamic SQL statement using UNPIVOT to be more efficient, though.

An inefficient approach, but one that is relatively easy to follow, would be something like:

<span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> ed<br/>Wrote </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">file</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> afiedt</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">buf<br/><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">create</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> replace type emp_unpivot_type<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">2</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">as</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> object </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">3</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  empno number</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  col varchar2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4000</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">5</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">);</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">/</span>

<span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">create</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">or</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> replace </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">function</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> unpivot_emp<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">2</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> p_empno </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">in</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> number </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">3</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">return</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> emp_unpivot_tbl<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  pipelined<br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">5</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">is</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">6</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  l_val varchar2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">4000</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">);</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">begin</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">8</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">for</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> cols </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">in</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> column_name </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> user_tab_columns </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">where</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> table_name </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">=</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'EMP'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">9</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  loop<br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">10</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">execute</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> immediate </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">'select '</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> cols</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">column_name </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">||</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">' from emp where empno = :empno'</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">11</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">into</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> l_val<br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">12</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">using</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> p_empno</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">13</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  pipe </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">row</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> emp_unpivot_type</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> p_empno</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> l_val </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">));</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">14</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">end</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> loop</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">15</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">return</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">16</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">end</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">/<br/><br/><span style="color: #242729; font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; font-size: 15px;">You can then call that in a SQL statement (I would think that you'd want at least a third column with the column name)<br/></span><br/><code style="font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif;"><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">  </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">select</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>  </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">2</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">*</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">   </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">from</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #101094;">table</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> unpivot_emp</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">))</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>SQL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">/</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/><br/>  EMPNO COL<br/></span><span class="com" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #858c93;">---------- ----------------------------------------</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> MILLER<br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> CLERK<br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7782</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">23</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">-</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;">JAN-82<br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">1301</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"><br/>   </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">7934</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #303336;"> </span><span class="lit" style="font-style: inherit; font-weight: inherit; font-size: inherit; font-family: inherit; color: #7d2727;">10</span>

https://stackoverflow.com/questions/15100101/unpivot-on-an-indeterminate-number-of-columns?answertab=votes#tab-top