title: How to get a unique count of decoded/unpivoted rows (column to row)
body: <p>Working with Oracle DB.<br>
  Having an issue converting an set of columns to rows and getting a unique count for each row.  </p>
  
  <p>I'm working with a table that identifies a set of values (up to six) that are attached to a specific account number.  I wanted to unpivot these six columns so that they are listed in there own row.  Each row would have a count representing the positional value (i.e., Value 1 = 1, Value 3 = 3, etc...)</p>
  
  <p>For example, I have a table like below. 
  The Values(1-6) will always be unique per Account Number</p>
  
  <pre><code>AccountNumber|Value1|Value2|Value3|Value4|Value5|Value6
  -------------------------------------------------------
  1            |123   |1234  |12345 |12    |12345 |1234
  2            |123   |1234  |12345 |12    |12345 |1234
  3            |123   |1234  |12345 |12    |12345 |1234
  </code></pre>
  
  <p>And I want the order of precedence to be attached to the Value number.  </p>
  
  <pre><code>AccountNumber|Order|Value
  -------------------------
  1            |1    |123
  1            |2    |1234
  1            |3    |12345
  1            |4    |12
  1            |5    |123456
  1            |6    |1
  2            |1    |123
  2            |2    |1234
  2            |3    |12345
  2            |4    |12
  2            |5    |123456
  2            |6    |1
  3            |1    |123
  3            |2    |1234
  3            |3    |12345
  3            |4    |12
  3            |5    |123456
  3            |6    |1
  </code></pre>
  
  <p>I have the following, </p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT
  '1' AS FACILITYID,
  HL.ACCOUNT_ID AS AccountNumber,
  --UNPIVOT_ROW,
  DECODE(UNPIVOT_ROW, 1, TDL.DX_ONE_ID,
                      2, TDL.DX_TWO_ID,
                      3, TDL.DX_THREE_ID,
                      4, TDL.DX_FOUR_ID,
                      5, TDL.DX_FIVE_ID,
                      6, TDL.DX_SIX_ID) AS Value,
  
  FROM ACCOUNT_LIST HL
  INNER JOIN TRANSACTIONS TRAN ON HL.ACCOUNT_ID = TRAN.ACCOUNT_ID,
  (SELECT LEVEL AS UNPIVOT_ROW FROM DUAL CONNECT BY LEVEL &lt;= 6)
  </code></pre>
  
  <p>I am able to unpivot (using DECODE(), UNPIVOT() was giving me issues) but I can't seem to wrap my head around associating a unique count.</p>
  
  <p>This is probably easy but it is just slipping out of my head trying to think of an efficient way to handle the 20,000+ plus rows before I unpivot without to much overhead.  </p>
  
  <p>I tried returning the value UNPIVOT_ROW and using a second decode to output a number but it just kept spitting out the number '1'</p>
  
  <p>Any help or suggestions would be greatly appreciated!</p>