title: Returning One Row with Multiple Column Positions as a List
body: <p>My problem seems pretty straight forward, but I'm struggling to grasp an easier way of fulfilling the required output.</p>
  
  <p><strong>Problem:</strong> I have entries in a table. These entries are identified by an ID column. They are joined with a table that shows a particular volunteer position that they hold or have held. If they are identified as having more than one position, their record returns multiple times.</p>
  
  <p><strong>Solution:</strong> I would like to list their positions in one column so that their entry based on the ID column returns only once. I have tried using STUFF and CONCAT, but all they return are multiples of the same position held for each entry as opposed to finding out if they have multiple positions and listing them, which means I'm probably using those functions incorrectly. </p>
  
  <p>Here is the current output: </p>
  
  <pre><code>ID         FIRST_NAME           current_flag Position
  ---------- -------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  106379     Christine            1            NOMAL
  106379     Christine            1            SFC
  106418     Mary                 1            CVP
  106751     Denise               1            SDFA
  106885     Marianna             1            RCRA
  107244     Jennifer             1            RCF
  </code></pre>
  
  <p>Here is the desired output:</p>
  
  <pre><code> ID         FIRST_NAME           current_flag Position
  ---------- -------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  106379     Christine            1            NOMAL,SFC
  106418     Mary                 1            CVP
  106751     Denise               1            SDFA
  106885     Marianna             1            RCRA
  107244     Jennifer             1            RCF
  </code></pre>
  
  <p>I have removed extraneous columns because the output is not conducive to showing what the desired output would be, but they are included in my code as follows.
  Current Code:</p>
  
  <pre><code>SELECT DISTINCT Name.ID, 
                  Name.FIRST_NAME, 
                  Name.LAST_NAME, 
                  Name_Address.ADDRESS_1, 
                  Name_Address.CITY, 
                  Name_Address.STATE_PROVINCE, 
                  Name_Address.ZIP, 
                  Name_Address.PREFERRED_MAIL,      
                  vcsiboCurrentCommittee.current_flag, 
                  CONVERT(VARCHAR(10), Name.BIRTH_DATE, 101) AS BirthDate,
                  vcsiboCurrentCommittee.Position
  FROM         Name INNER JOIN
               Name_Address ON Name.ID = Name_Address.ID INNER JOIN
               vcsiboCurrentCommittee ON Name.ID = vcsiboCurrentCommittee.ID
  WHERE     (Name_Address.PREFERRED_MAIL = 1.00) 
            AND (vcsiboCurrentCommittee.current_flag = 1.00) 
            AND (Name.ID &lt;&gt; 10) AND (Name.BIRTH_DATE &lt;&gt; '') 
            AND (vcsiboCurrentCommittee.CommitteeCode IN 
           ('ALUMNAE_DEPT', 'COLLEGIATE-DEPT', 'EDUCATION', 'FINANCIAL', 'INTERN_COUNCIL', 'IPDEPT', 'MEMBERSHIP','PANHELLENIC', 'REG_1', 'REG_2', 'REG_3', 'REG_4', 'REG_5', 'REG_6', 'REG_7', 'REG_8'))
  </code></pre>
  
  <p>Code using STUFF: </p>
  
  <pre><code>SELECT DISTINCT Name.ID, 
                  Name.FIRST_NAME, 
                  Name.LAST_NAME, 
                  Name_Address.ADDRESS_1, 
                  Name_Address.CITY, 
                  Name_Address.STATE_PROVINCE, 
                  Name_Address.ZIP,                            
                  Name_Address.PREFERRED_MAIL,
                  vcsiboCurrentCommittee.current_flag,
                  CONVERT(VARCHAR(10), Name.BIRTH_DATE, 101) AS BirthDate,
                  STUFF(
                  (SELECT
                  CAST(',' AS varchar(max)) + vcsiboCurrentCommittee.Position
                  FROM vcsiboCurrentCommittee AS vcc
                  WHERE vcc.ID = Name.ID
                  ORDER BY vcc.Position
                  FOR xml path('')
                  ), 1, 1, '') AS Positions,
              vcsiboCurrentCommittee.Position
  FROM Name INNER JOIN
       Name_Address ON Name.ID = Name_Address.ID INNER JOIN
       vcsiboCurrentCommittee ON Name.ID = vcsiboCurrentCommittee.ID
  WHERE (Name_Address.PREFERRED_MAIL = 1.00) AND   
        (vcsiboCurrentCommittee.current_flag = 1.00) 
        AND (Name.ID &lt;&gt; 10) AND (Name.BIRTH_DATE &lt;&gt; '') 
        AND (vcsiboCurrentCommittee.CommitteeCode IN ('ALUMNAE_DEPT', 'COLLEGIATE-DEPT', 'EDUCATION', 'FINANCIAL', 'INTERN_COUNCIL', 'IPDEPT', 'MEMBERSHIP', 'PANHELLENIC', 'REG_1', 'REG_2', 'REG_3', 'REG_4', 'REG_5', 'REG_6', 'REG_7', 'REG_8'))
  </code></pre>
  
  <p>That creates this ouput: </p>
  
  <pre><code>ID         FIRST_NAME           current_flag Positions                                                                                                                                                                                                                                                        Position
  ---------- -------------------- ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  106379     Christine            1            NOMAL,NOMAL,NOMAL,NOMAL,NOMAL,NOMAL,NOMAL,NOMAL,NOMAL,NOMAL                                                                                                                                                                                                      NOMAL
  106379     Christine            1            SFC,SFC,SFC,SFC,SFC,SFC,SFC,SFC,SFC,SFC                                                                                                                                                                                                                          SFC
  106418     Mary                 1            CVP,CVP,CVP,CVP,CVP,CVP,CVP,CVP,CVP,CVP,CVP,CVP,CVP                                                                                                                                                                                                              CVP
  106751     Denise               1            SDFA,SDFA,SDFA,SDFA,SDFA,SDFA,SDFA,SDFA,SDFA,SDFA,SDFA 
  </code></pre>
  
  <p>Thanks in advance.
  SQL Server 2012</p>