SQL FOR XML splicing column data of the same class

The database records the work experience of the employees, one through the data, the existing demand for Excel to export the work experience of employees, so you have to put the work experience together

The test data table is as follows

The results that need to be processed are as follows (multiple experiences are separated by commas):

SQL statements:

Select EmpID, Left (Workexperience,len (workexperience) – 1) as Workexperience from  (select EmpID, (select experience+ + ', ' from dbo. The Employee WHERE EmpID =a.empid for XML PATH (")) as workexperience from dbo. Employee A GROUP by A.empid) B

The query result of Select is returned as a rowset, but you can also specify a FOR XML clause in SQL to retrieve the query as XML. In the FOR XML clause, you can specify one of the following modes:RAW, AUTO, explicit, and path.

Raw mode:

SELECT TOP 3 id,name,clientcode from dbo. Customer for XML RAW

Results:

<row id= "94" Name= "1st Paygateway.net" clientcode= "c-na-tl-0063"/><row id= "" name= "3D NURI CO LTD" Clientcode = "c-as-tl-0049"/><row id= "" "Name=" TV "clientcode=" c-na-tl-0064 "/>

Raw mode:

SELECT TOP 3 id,name,clientcode from dbo. Customer for XML AUTO

Results:

<dbo. Customer id= "94" Name= "1st Paygateway.net" clientcode= "c-na-tl-0063"/><dbo. Customer id= "name=" 3D NURI CO LTD "clientcode=" c-as-tl-0049 "/><dbo. Customer id= "name=" clientcode= "c-na-tl-0064"/>

The relevant SQL for XML is referenced by: http://www.cnblogs.com/kenshincui/archive/2011/12/31/2309217.html

SQL FOR XML splicing column data of the same class