将查询结果行展平为具有逗号分隔值的列

发布于 2024-10-22 00:55:30 字数 2164 浏览 0 评论 0原文

我有这个查询：

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'  
)

返回一个像这样的结果集

9876543A 123456789 2011年2月13日
姓氏名字 011 注册已提交 61 03/01/2011 002 NULL 02/04/2011 MARKW 申请 9876543A 123456789 2011年2月13日
姓氏名字 011 注册已提交 61 03/01/2011 002 NULL 02/08/2011 MARKW 新身份证 9876543A 123456789 2011年2月13日
姓氏名字 011 注册已提交 61 03/01/2011 002 NULL 02/08/2011 MCSB473 信息发送至 CMS 9876543A 123456789 02/13/2011
姓氏名字 011 注册已提交 61 03/01/2011 002 空 2011 年 2 月 8 日收到 MCSB475 回复来自内容管理系统

我试图将结果扁平化为一行，其中 EventDate、UserID 和 Description 列以逗号分隔，并且结果适合一行，仅使用 GROUP BY 来扁平化所有内容。我陷入困境 - 使用 XML PATH 和 COALESCE 没有按照我希望的方式工作......

原文

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...

分享到QQ

分享到微博