title: Combine two rows with shared column values in Business Objects
body: <p>Currently I have a report which looks like this:</p>
  
  <pre><code>C1   C2   Var1     Var2
  a    b     A       &lt;empty&gt;
  a    b    &lt;empty&gt;    B
  a    b    &lt;empty&gt;    C
  c    d     A       &lt;empty&gt;
  e    f    &lt;empty&gt;    B
  </code></pre>
  
  <p>In my data set, there are records which share the same values in C1 and C2, but differ in a third column, C3. Var1 and Var2 are variables that use C3. The variables are defined as follows:</p>
  
  <pre><code>Var1 = [C3] Where ([C3] InList ("A"))
  Var2 = [C3] Where ([C3] InList ("B";"C"))
  </code></pre>
  
  <p>I would like to combine the rows where the values in columns C1 and C2 are the same, and combine Var1 and Var2 on the same row if one of them is empty. I would like the report to display in the following manner:</p>
  
  <pre><code>C1   C2   Var1     Var2
  a    b     A         B
  a    b     A         C
  c    d     A       &lt;empty&gt;
  e    f    &lt;empty&gt;    B
  </code></pre>
  
  <p>This is effectively the same as the following query in SQL:</p>
  
  <pre><code>Select T1.C1, T1.C2, T2.C3, T3.C3 from tableone T1
  Left Join tabletwo T2 on
  T1.C1 = T2.C1 and T1.C2 = T2.C2 and T2.C3 = 'A'
  Left Join tabletwo T3 on
  T1.C1 = T3.C1 and T1.C2 = T3.C2 and T3.C3 in ('B','C');
  </code></pre>
  
  <p>In the SQL above, tableone has one row for each unique (C1, C2) pair. In tabletwo, there can be multiple rows with different C3 values that share C1 and C2.</p>
  
  <p>Is there a way in Business Objects to combine rows where one row has an empty Var1 or Var2 and the other has a value in the same column?</p>