使用查询合并SQL Server 2012中的记录 [英] Merge Records in SQL Server 2012 Using query

问题描述

大家好，

我有以下表值，我想合并Error_message并且只显示2条记录和逗号分隔的第二条记录因为SlNo。有2条记录



Sl No. Emp_No Error_Message

1 512542 INVALID EMP NO（EMP NO NOTES BELONGS选择客户）

2 385539员工已经支付现金支付

2 385539已经为所选客户提供的记录，EMPLOYEE_ID，

TRANSCATION_MONTH AND TRANSCATION_YEAR



输出应为以下格式



Sl No. Emp_No Error_Message

1 512542无效EMP否（EMP不属于选择的客户）

2 385539 ​​1.员工已经支付现金支付

2.已为所选客户提供的记录，EMPLOYEE_ID，

TRANSCATION_MONTH和TRANSCATION_YEAR



使用以下Quer y，但根据要求不能正常工作

Hi All,
I have Below table value, i want to merge the Error_message and show only 2 records with comma seperated for second record because SlNo. have 2 records

Sl No. Emp_No Error_Message
1 512542 INVALID EMP NO (EMP NO DOES NOT BELONGS TO SELECTED CLIENT)
2 385539 PAYELEMENT ALREADY EXIST FOR THE Employee
2 385539 RECORD ALREADY EXIST FOR THE SELECTED CLIENT, EMPLOYEE_ID,
TRANSCATION_MONTH AND TRANSCATION_YEAR

The output should be the below format

Sl No. Emp_No Error_Message
1 512542 INVALID EMP NO (EMP NO DOES NOT BELONGS TO SELECTED CLIENT)
2 385539 1. PAYELEMENT ALREADY EXIST FOR THE Employee
2. RECORD ALREADY EXIST FOR THE SELECTED CLIENT, EMPLOYEE_ID,
TRANSCATION_MONTH AND TRANSCATION_YEAR

Am using below Query, but its not working properly as per requirment

SELECT t1.PersonID,
       Units =REPLACE( (SELECT Unit AS [data()]
           FROM mytable t2
          WHERE t2.PersonID = t1.PersonID
          ORDER BY Unit
            FOR XML PATH('')
            ), ' ', ',')
      FROM mytable t1
      GROUP BY PersonID ;

推荐答案

试试如下。但是这个代码只要有一个记录就可以了1.

Try like below. But this code put 1. even if there is a single record

DECLARE @TABLE TABLE (SlNo TINYINT, EmpNo INT, ErrorMessage VARCHAR(500))
INSERT INTO @TABLE VALUES (1, 512542, 'INVALID EMP NO (EMP NO DOES NOT BELONGS TO SELECTED CLIENT)')
,(2, 385539, 'PAYELEMENT ALREADY EXIST FOR THE Employee')
,(3, 385539, 'RECORD ALREADY EXIST FOR THE SELECTED CLIENT, EMPLOYEE_ID, TRANSCATION_MONTH AND TRANSCATION_YEAR')

SELECT * FROM @Table


SELECT EmpNo
       , (SELECT CAST(Row_NUMBER () OVER ( ORDER BY SlNo )  AS VARCHAR) + '. ' + te.ErrorMessage + ' '
          FROM @Table te
          WHERE te.EmpNo = t.EmpNo
          FOR XML PATH(''))
FROM @Table t
GROUP BY EmpNo

这篇关于使用查询合并SQL Server 2012中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！