title: SQL Unpivot / Coalesce multiple columns into one column based on values
body: <p>I am trying to unpivot / coalesce multiple columns into one value, based on values in the target columns. </p>
  
  <p>Given the following sample data:</p>
  
  <pre><code>CREATE TABLE SourceData (
      Id INT
     ,Column1Text VARCHAR(10)
     ,Column1Value INT
     ,Column2Text VARCHAR(10)
     ,Column2Value INT
     ,Column3Text VARCHAR(10)
     ,Column3Value INT
  )
  
  INSERT INTO SourceData
  SELECT 1, NULL, NULL, NULL, NULL, NULL, NULL UNION 
  SELECT 2, 'Text', 1, NULL, NULL, NULL, NULL UNION
  SELECT 3, 'Text', 2, 'Text 2', 1, NULL, NULL UNION
  SELECT 4, NULL, NULL, NULL, 1, NULL, NULL
  </code></pre>
  
  <p>I am trying to produce the following result set:</p>
  
  <pre><code>Id          ColumnText
  ----------- ----------
  1           NULL
  2           Text
  3           Text 2
  4           NULL
  </code></pre>
  
  <p>Where ColumnXText column values become one "ColumnText" value per row, based on the following criteria:</p>
  
  <ol>
  <li>If all ColumnX columns are NULL, then ColumnText = NULL </li>
  <li>If a ColumnXValue value is "1" and the ColumnXText IS NULL, then
  ColumnText = NULL</li>
  <li>If a ColumnXValue value is "1" and the ColumnXText IS NOT NULL, then
  ColumnText = ColumnXText.</li>
  </ol>
  
  <p>There are no records with more than one ColumnXValue of "1".</p>
  
  <p>What I'd tried is in this SQL Fiddle @ <a href="http://sqlfiddle.com/#!6/f2e18/2" rel="nofollow noreferrer">http://sqlfiddle.com/#!6/f2e18/2</a></p>
  
  <p>I'd tried (shown in SQL fiddle):
  Unpivioting with CROSS / OUTER APPLY. I fell down on this approach because I was not able to get WHERE conditions to produce the expected results.</p>
  
  <p>I'd also tried using UNPIVOT, but had no luck. </p>
  
  <p>I was thinking of a brute-force approach that did not seem to be correct. The real source table has 44MM rows. I do not control the schema of the source table.</p>
  
  <p>Please let me know if there's a simpler approach than a brute-force tangle of CASE WHENs. Thank you.</p>