sql group 列的叠加

 

 

/**

*new Vendor 报表

*/

 

ALTER VIEW V_R_VendorMOD

AS     

SELECT  pa.CNAME ,

pa.CNAMEMOD,

pa.ENAME ,

pa.ENAMEMOD,

pa.Nickname ,

pa.NicknameMOD,

pa.tvacc_code ,

p.CNAME AS pcname ,

p1.CNAME AS pcnamemod,

t.CNAME AS tcname ,

t1.CNAME AS tcnamemod,

ds.OPA08Desc ,

ds1.OPA08Desc AS OPA08Descmod,

pa.[ADDRESS] ,

pa.ADDRESSMOD,

pa.Remark ,

pa.RemarkMOD,

pa.MINE ,

pa.MINEMOD,

pa.minePhone ,

pa.minePhoneMOD,

pa.MinePhoneext ,

pa.MinePhoneextMOD,

pa.TaskID ,

pat.media,

pat.mediamod,

pat.mediaNameCancelMOD,

LTRIM(RTRIM(ts.TASK_Initiator)) AS TASK_Initiator ,

ts.TASK_CreateTime ,

ts.TASK_FinishTime 

 

FROM    dbo.NMS_Form_Payee pa

LEFT JOIN CAMSDB.dbo.TOWN t ON t.TOWN_ID = pa.TOWN_ID

LEFT JOIN CAMSDB.dbo.PROVINCE p ON p.PROVINCE_ID = t.PROVINCE_ID

LEFT JOIN CAMSDB.dbo.OPA08_DataState ds ON ds.OPA08ID = pa.OPA08ID

 

LEFT JOIN CAMSDB.dbo.TOWN t1 ON t1.TOWN_ID = pa.TOWN_IDMOD

LEFT JOIN CAMSDB.dbo.PROVINCE p1 ON p1.PROVINCE_ID = t1.PROVINCE_ID

LEFT JOIN CAMSDB.dbo.OPA08_DataState ds1 ON ds1.OPA08ID = pa.OPA08IDMOD

 

INNER JOIN dbo.SYS_INST_TASKS ts ON ts.TASK_ID = pa.TaskID AND ts.TASK_WFNAME=N'Vendor Data Change' AND ts.TASK_Finished=1

LEFT JOIN     (SELECT  TaskID ,

media = REPLACE(( SELECT    '' + mediaName

FROM      NMS_Form_PayeeAccount

WHERE     TaskID = pata.TaskID

FOR

XML PATH('')

),'
','  '),

mediamod = REPLACE(( SELECT    '' + mediaNameMOD

FROM      NMS_Form_PayeeAccount

WHERE     TaskID = pata.TaskID

FOR

XML PATH('')

),'
','  '),

mediaNameCancelMOD= REPLACE(( SELECT    '' + mediaNameCancelMOD

FROM      NMS_Form_PayeeAccount

WHERE     TaskID = pata.TaskID

FOR

XML PATH('')

),'
','  ')

FROM    NMS_Form_PayeeAccount pata

GROUP BY pata.TaskID ) AS pat ON pat.TaskID = pa.TaskID 

AND ts.TASK_Finished = 1

WHERE   pa.P_ID IS  NOT NULL 

 

GO