title: Making multiple rows with few columns into one row with many columns (Oracle view)
body: <p>What I had, for example, was a table that looks like:</p>
  
  <pre><code>ID varchar2(6)
  ERROR_TYPE varchar2(30)
  ERROR_CODE varchar2(6)
  ERROR_DESCRIPTION varchar2(50)
  REVIEWED_FLAG varchar2(1)
  </code></pre>
  
  <p>The ID is just the typical random ID, ERROR_TYPE is the functionality that caused the error, say an order or a shipment.  REVIEWED_FLAG is 'Y' or 'N', ERROR_DESCRIPTION is pretty much just that, and ERROR_CODE is a code such as 1A or 2B with:<br>
  0 - it passed/there is no error, or <br>
  1 - it failed/there is an error<br>
  And the full error code would look like 1A=0, or 2B=1 (not my choice, this is what I was given to deal with).<p>
  A standard </p>
  
  <pre><code>select ID, ERROR_CODE from ERROR_TABLE where ERROR_TYPE = 'SHIPMENT'
  </code></pre>
  
  <p>will return a result set like:<br></p>
  
  <pre><code>ID      ERROR_CODE  ERROR_TYPE
  100001  1A=0        SHIPMENT
  100001  2B=1        SHIPMENT
  100001  3A=1        SHIPMENT
  100001  4B=0        SHIPMENT
  100002  1A=1        SHIPMENT
  100002  2B=1        SHIPMENT
  100002  3A=0        SHIPMENT
  100002  4B=0        SHIPMENT
  </code></pre>
  
  <p>What the user wanted to see was:</p>
  
  <pre><code>ID       ERROR_TYPE  ERROR_CODE_1A  ERROR_CODE_2B  ERROR_CODE_3A  ERROR_CODE_4B
  1000001  SHIPMENT          0              1              1              0
  1000002  SHIPMENT          1              1              0              0
  </code></pre>
  
  <p>What I've seen about PIVOT is that it only works with aggregate functions.  And this isn't concatenation because the values are in their own columns.</p>
  
  <p>I ended up creating a view that's basically</p>
  
  <pre><code>create view V_SHIPMENT_ERRORS as
  with ERROR1A as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_1A from error_table 
                    where error_type = 'SHIPMENT' and error_code like '1A%'),
  with ERROR2B as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_2B from error_table 
                    where error_type = 'SHIPMENT' and error_code like '2B%'),
  with ERROR3A as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_3A from error_table 
                    where error_type = 'SHIPMENT' and error_code like '3A%'),
  with ERROR4B as (select ID, substr(ERROR_CODE, 4) as ERROR_CODE_4B from error_table 
                    where error_type = 'SHIPMENT' and error_code like '4B%')
  select a.ID, error_code_1A, error_code_2B, error_code_3A, error_code_4B
    from error1a a, error2b b, error3a c, error4b d
   where a.id = b.id
     and a.id = c.id
     and a.id = d.id;
  </code></pre>
  
  <p>And this does give me the desired result set.</p>
  
  <p>The basic question - Is there a more efficient way of doing row/column manipulation like this with only SQL?  </p>
  
  <p>And a comment to this is if someone is looking to do the same thing I hope what I got to work at least helps them out.  I couldn't find this type of solution anywhere and it was a combination of ideas on multiple sites that led to this.</p>