title: Flatten Query Result Rows Into Columns with Comma Separted Values
body: <p>I have this query:</p>
  
  <pre><code>SELECT 
      HICNo
      ,trr.CUS_ID
      ,TRRRunDate
      ,trr.LastName
      ,trr.FirstName
      ,trr.TRCCode
      ,trr.TRCDescr
      ,trr.TCCode
      ,trr.TRREffDate
      ,PBPID
      ,PriorPBPID
      ,LISLevel
      ,[LIS%]
      ,LISEffDate
      ,CONVERT (CHAR,INE_EV_DT,101) AS EventDate
      ,INE_USER_ID AS UserID
      ,tcl.TCL_TYPE_DESC AS [Description]
  FROM 
      #AM_TRR_INS trr                                          
      JOIN ETLStaging.dbo.INS_INSURED ins ON trr.CUS_ID   = ins.INS_CUS_ID
      JOIN ETLStaging.dbo.INE_INSURED_EV ine ON ins.INS_ID = INE.INE_INS_ID
      JOIN ETLStaging.dbo.TCL_TYPE_CD_LOOKUP tcl ON 
              ine.INE_TYPE = tcl.TCL_TYPE_CODE
              AND tcl.TCL_ID = '12'
  UNION 
  (
      SELECT
          HICNo
          ,trr.CUS_ID
          ,TRRRunDate
          ,trr.LastName
          ,trr.FirstName
          ,trr.TRCCode
          ,trr.TRCDescr
          ,trr.TCCode
          ,trr.TRREffDate
          ,PBPID
          ,PriorPBPID
          ,LISLevel
          ,[LIS%]
          ,LISEffDate
          ,CONVERT (CHAR,INA_PRC_DT,101) AS EventDate
          ,ina.INA_USER_ID AS UserID
          ,tcla.TCL_TYPE_DESC AS [Description]
  
      FROM 
          #AM_TRR_INS trr                                          
          JOIN ETLStaging.dbo.INS_INSURED ins ON trr.CUS_ID   = ins.INS_CUS_ID
          JOIN ETLStaging.dbo.INA_INSURED_AUD ina ON ins.INS_ID= ina.INA_INS_ID
          JOIN ETLStaging.dbo.TCL_TYPE_CD_LOOKUP tcla ON 
              ina.INA_TYPE = tcla.TCL_TYPE_CODE
              AND tcla.TCL_ID = '12'  
  )
  </code></pre>
  
  <p>That returns a result set like so</p>
  
  <blockquote>
    <p>9876543A  123456789   02/13/2011<br>
      LASTNAME    FIRSTNAME   011 Enrollment Accepted as Submitted    61  03/01/2011
      002             NULL    02/04/2011                      MARKW       APPLICATION
    9876543A  123456789   02/13/2011<br>
      LASTNAME    FIRSTNAME   011 Enrollment Accepted as Submitted    61  03/01/2011
      002             NULL    02/08/2011                      MARKW       NEW ID CARD
    9876543A  123456789   02/13/2011<br>
      LASTNAME    FIRSTNAME   011 Enrollment Accepted as Submitted    61  03/01/2011
      002             NULL    02/08/2011                      MCSB473     INFORMATION
    SENT TO CMS 9876543A  123456789   02/13/2011<br>
      LASTNAME    FIRSTNAME   011 Enrollment Accepted as Submitted    61  03/01/2011
      002             NULL    02/08/2011                      MCSB475     REPLY RECEIVED
    FROM CMS</p>
  </blockquote>
  
  <p>I'm trying to flatten the results down to one line where the EventDate, UserID and Description columns are comma delimited and the results fit into one row and just using GROUP BY to flatten everything. I'm stuck- using XML PATH and COALESCE aren't working how I'd hoped...</p>