Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,377 Comments

Discussions

How To Transpose Columns To Rows?

simon87
simon87 Member Posts: 55 Blue Ribbon
edited Dec 13, 2017 12:44PM in SQL & PL/SQL

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:

campoid_solicitudmonto_solicitud
campo11100
campo21100

How could I move the fields in the following way?

campovalor
campo11
campo2100
Tagged:

Best Answer

  • simon87
    simon87 Member Posts: 55 Blue Ribbon
    edited Dec 13, 2017 12:44PM Accepted Answer

    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

    simon87

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 10, 2017 10:59PM
    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
    simon87 Member Posts: 55 Blue Ribbon
    edited Dec 10, 2017 11:03PM

    Hi,

    Sorry,

    It must be kept.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Dec 10, 2017 11:14PM
    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
    Etbin Member Posts: 8,968 Gold Crown
    edited Dec 11, 2017 2:16AM

    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

    CAMPOVALOR
    campo11
    campo2100

    Regards

    Etbin

  • simon87
    simon87 Member Posts: 55 Blue Ribbon
    edited Dec 13, 2017 12:44PM Accepted Answer

    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

    simon87
This discussion has been closed.