title: SQL Server T-SQL: trouble grouping data from two tables
body: <p>I have a table <code>ListofString</code> with these columns:</p>
  <pre><code>parent_id
  parent_prop_id
  element_value
  </code></pre>
  <p>My data looks like this (I've excluded the bulk for simplicity)</p>
  <pre><code>parent_id   parent_prop_id  element_value
  ------------------------------------------
  C4236016    BEAE885B        acw1
  C4236016    BEAE885B        acw2
  C4236016    BEAE885B        acw3
  C4236016    BEAE885B        acw11
  C4236016    1DC045A5        mmw1
  C4236016    1DC045A5        mmw2
  C4236016    1DC045A5        mmw3
  </code></pre>
  <p>In a select, I'd like to get this:</p>
  <pre><code>parent_id   String1         String2
  ------------------------------------------------
  C4236016    mmw1|mmw2|mmw3  acw1|acw2|acw3|acw11
  </code></pre>
  <p>Instead I get this:</p>
  <pre><code>parent_id   String1         String2
  ------------------------------------------------
  C4236016                    acw1|acw2|acw3|acw11
  C4236016    mmw1|mmw2|mmw3
  
      
  </code></pre>
  <p>Here's what I've tried:</p>
  <pre><code>SELECT 
      LOS0.parent_id,
      String1 = CASE 
                   WHEN LOS0.parent_prop_id = '1DC045A5' 
                      THEN STUFF((SELECT '|' + LOS1.element_value 
                                  FROM ListOfString LOS1
                                  WHERE LOS0.parent_prop_id = LOS1.parent_prop_id
                                  ORDER BY LOS0.parent_prop_id
                                  FOR XML PATH('')), 1, LEN(','), '')
                      ELSE '' 
                END,
      String2 = CASE 
                   WHEN LOS0.parent_prop_id = 'BEAE885B' 
                      THEN STUFF((SELECT '|' + LOS1.element_value 
                                  FROM ListOfString LOS1
                                  WHERE LOS0.parent_prop_id = LOS1.parent_prop_id
                                  ORDER BY LOS0.parent_prop_id
                                  FOR XML PATH('')), 1, LEN(','), '')
                      ELSE '' 
                 END
  FROM 
      ListOfString LOS0
  GROUP BY 
      LOS0.parent_id, LOS0.parent_prop_id
  </code></pre>
  <p>Any ideas how I can include these two rows into the same line?
  If I remove that group by of parent_prop_id, I get the message:
  Column 'ListOfString.parent_prop_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  <p>Thanks</p>
  <p>Sorry if I didn't format this correctly.</p>
  <p>Martin</p>