Use SQL SERVER for XML path to convert multiple result sets to one row and redo

a system in a pharmaceutical industry requires a collection of different contact types based on the patient's contact record ID, and the effect is like this

--patient exposure to record information, a patient can have n different contact records, and each contact record has n contact type recordsIF object_id('dbo. TEST') is  not NULLDROP TABLEdbo. TESTGOCREATE TABLEdbo. TEST (IDINT IDENTITY( +,1) not NULL, CIDINT,--Contact record numberREMARKVARCHAR(4000), ContactTypeVARCHAR( -), DESCRIBEVARCHAR(4000), AdddateDATETIME)GO--test data, including duplicate typesINSERT  intodbo. TEST (CID, REMARK, ContactType, DESCRIBE, adddate)VALUES(81667,'Consulting Notes','Contacttype_1',NULL,'2014-06-03 09:53:24'),(81667,'return Comment','contacttype_2',NULL,'2014-06-03 09:53:24'),(81667,'Consulting Notes','Contacttype_1',NULL,'2014-06-03 09:53:24.92'),(81667,'return Comment','contacttype_2','return to look for Brown','2014-06-03 09:53:24.927'),(81667,'follow -up notes','Contacttype_3','follow -up details','2014-06-03 09:53:24.933'),(81667,'Notification Notes','Contacttype_4','description of the notification content','2014-06-03 09:53:24.94'),(81667,'Reservation Notes','contacttype_5','Reservation Content','2014-06-03 09:53:24.947'),(81667,'Reply to comments','Contacttype_6','reply test .... ','2014-06-03 09:53:24.95'),(81679,'Consulting Notes','Contacttype_1',NULL,'2014-06-03 10:53:53.743'),(81679,'return Comment','contacttype_2','Return Content Description','2014-06-03 10:53:53.75'),(81679,'follow -up notes','Contacttype_3','follow -up details','2014-06-03 10:53:53.757'),(81679,'Notification Notes','Contacttype_4','description of the notification content','2014-06-03 10:53:53.763'),(81679,'Reservation Notes','contacttype_5','Reservation Content','2014-06-03 10:53:53.767'),(81679,'Reply to comments','Contacttype_6','Reply Content','2014-06-03 10:53:53.777')

Previously written in a SQL method is such that there is no problem under normal circumstances, but if a contact record has two of the same contact type, there will be the same contact type (this problem may be caused by code, but I can not directly change the code) since the code can not be changed that can only be processed through the database

--before the SQL method, there can be returns of the same type thenDECLARE @SNvarchar( -)SET @S="'SELECT @S=@S+'/'+( CaseContactType when 'Contacttype_1' Then 'Consulting' when 'contacttype_2' Then 'return Visit' when 'Contacttype_3' Then 'Follow -up' when 'Contacttype_4' Then 'Notice' when 'contacttype_5' Then 'Reservation' when 'Contacttype_6' Then 'reply'END) fromTestWHERECid=81667PRINT @s

The following effect is obviously not what I want through a few hours of hard work finally through the SQL perfect solution, the time wasted on the problem of the weight of the solution 1. In the previous SQL directly joined distinct for data deduplication but always error "there is a syntax error near the keyword ' DISTINCT '. Severity"2. Using a temporary table, first query the data and insert the temporary table, and then loop the temporary table and add to the string, when added, if present does not repeat add 3. First, you get the string that is likely to be duplicated, and then you redo the string by 4. Use my current method for XML andDISTINCTget the string that has been weighed and then assign a variable For XML requires SQL Server 2005+ version support

--new SQL method to de-redo with for XML path and distinctDECLARE @SNvarchar( -)--The result cannot be assigned to a variable in a query that contains the DISTINCT keyword, and the following SQL will appear with a syntax error near the "keyword ' DISTINCT '." Severity "--SELECT @s= DISTINCT--('/' + (case contacttype – When ' contacttype_1 ' Then ' Consulting ' – when ' contacttype_2 ' then ' return visit '--W  HEN ' contacttype_3 ' then ' follow-up '-when ' contacttype_4 ' and then ' notice '-when ' contacttype_5 ' then ' appointment '-when ' contacttype_6 ' then ' Reply '--END)--from Test WHERE cid=81667--for XML PATH (')SELECT @S=(SELECT DISTINCT ('/'+( CaseContactType when 'Contacttype_1' Then 'Consulting' when 'contacttype_2' Then 'return Visit' when 'Contacttype_3' Then 'Follow -up' when 'Contacttype_4' Then 'Notice' when 'contacttype_5' Then 'Reservation' when 'Contacttype_6' Then 'reply'END)) fromTestWHERECid=81667 forXML PATH ("'))PRINT @s

Through the FOR XML andDISTINCTthe effect after the weight you can see that using the FOR XML path makes it easy to convert a multiple-selection result set into a rowReference: Blog Park-Flexible use of SQL SERVER for XML PATH MSDN using for XML to construct XML

From for notes (Wiz)

Use SQL SERVER for XML path to convert multiple result sets to one row and redo