title: t-SQL Combine multiple rows to one row using key field
body: <p>I have 2 temporary tables </p>
  
  <pre><code>#data:
  Key     UserID      Driver     Defect1     Defect2     Defect3    Defect4
  -------------------------------------------------------------------------
  124      151        Paul       Wheels      Windows      Null       Null
  124      151        Paul       Null        Null         Null       Null
  
  #data2:
  Key     Defect5     Defect6    Defect7
  --------------------------------------
  124     Seats       Doors     Hood
  </code></pre>
  
  <p>I am looking for a way to combine these 3 rows into a single row based on the Key field, so just rolling them up, ignoring the nulls and moving everything to a single row.  I have pivotted the original data to get it to this point, and am looking for the next step to finish it off please.</p>
  
  <p>I am hoping to get to:</p>
  
  <pre><code>#data2:
  Key  UserID  Driver Defect1 Defect2 Defect3  Defect4 Defect5  Defect6  Defect7
  ------------------------------------------------------------------------------
  124  151     Paul   Wheels  Windows Null     Null    Seats    Doors    Hood
  </code></pre>
  
  <p>Can anyone suggest a way please? </p>
  
  <p>My defects are incremental based upon a count of the rows (before the pivot) using:  </p>
  
  <pre><code>SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + PIVOT_CODE + ']' 
  ,@select_list = COALESCE(@select_list + ', ', '') + 
          'LEFT([' + PIVOT_CODE + '], CHARINDEX(''|'', ' + PIVOT_CODE + ')-1) AS [Defect_Item_' + SUBSTRING(PIVOT_CODE, 2, 4) + '] 
          '
  FROM (
     SELECT DISTINCT PIVOT_CODE
     FROM (
        SELECT 'r' + CAST(ROW_NUMBER() OVER (PARTITION BY formresultkey ORDER BY Defect_Item, Defect_description) AS VARCHAR(4)) AS PIVOT_CODE
        FROM #Data3
     ) AS rows
  ) AS PIVOT_CODES
  </code></pre>