I have this query:
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'
)
That returns a result set like so
9876543A 123456789 02/13/2011
LASTNAME FIRSTNAME 011 Enrollment Accepted as Submitted 61 03/01/2011 002 NULL 02/04/2011 MARKW APPLICATION 9876543A 123456789 02/13/2011
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
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
LASTNAME FIRSTNAME 011 Enrollment Accepted as Submitted 61 03/01/2011 002 NULL 02/08/2011 MCSB475 REPLY RECEIVED FROM CMS
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...
