使用SQL SERVER FOR XML PATH將多個結果集轉換成一行並進行去重處理

標籤：des   style   blog   http   color   ar   os   使用   for   

在一個醫藥行業的系統中需要根據患者的接觸記錄ID擷取不同接觸類型的集合，效果像這樣  

--患者接觸記錄資訊,一個患者可以有N個不同的接觸記錄,每個接觸記錄又有N個接觸類型記錄IF OBJECT_ID (‘dbo.TEST‘) IS NOT NULLDROP TABLE dbo.TESTGOCREATE TABLE dbo.TEST(ID INT IDENTITY (1000,1) NOT NULL,cid INT,--接觸記錄號REMARK VARCHAR (4000),CONTACTTYPE VARCHAR (20),DESCRIBE VARCHAR (4000),ADDDATE DATETIME)GO--測試資料,包含重複類型INSERT INTO dbo.TEST (cid, REMARK, CONTACTTYPE, DESCRIBE, ADDDATE)VALUES (81667,‘諮詢備忘‘,‘ContactType_1‘, NULL,‘2014-06-03 09:53:24‘),(81667,‘回訪備忘‘,‘ContactType_2‘, NULL,‘2014-06-03 09:53:24‘),(81667,‘諮詢備忘‘,‘ContactType_1‘, NULL,‘2014-06-03 09:53:24.92‘),(81667,‘回訪備忘‘,‘ContactType_2‘,‘回訪找棕‘,‘2014-06-03 09:53:24.927‘),(81667,‘隨訪備忘‘,‘ContactType_3‘,‘隨訪詳情‘,‘2014-06-03 09:53:24.933‘),(81667,‘通知備忘‘,‘ContactType_4‘,‘通知內容描述‘,‘2014-06-03 09:53:24.94‘),(81667,‘預約備忘‘,‘ContactType_5‘,‘預約內容‘,‘2014-06-03 09:53:24.947‘),(81667,‘回複備忘‘,‘ContactType_6‘,‘回複測試。。。。‘,‘2014-06-03 09:53:24.95‘),(81679,‘諮詢備忘‘,‘ContactType_1‘, NULL,‘2014-06-03 10:53:53.743‘),(81679,‘回訪備忘‘,‘ContactType_2‘,‘回訪內容描述‘,‘2014-06-03 10:53:53.75‘),(81679,‘隨訪備忘‘,‘ContactType_3‘,‘隨訪詳情‘,‘2014-06-03 10:53:53.757‘),(81679,‘通知備忘‘,‘ContactType_4‘,‘通知內容描述‘,‘2014-06-03 10:53:53.763‘),(81679,‘預約備忘‘,‘ContactType_5‘,‘預約內容‘,‘2014-06-03 10:53:53.767‘),(81679,‘回複備忘‘,‘ContactType_6‘,‘回複內容‘,‘2014-06-03 10:53:53.777‘)

 

之前寫的一個Sql方法裡是這樣的在正常的情況下沒有問題，但如果一個接觸記錄存在兩個相同的接觸類型的話就會存在相同的接觸類型(此問題有可能是代碼導致的,但我並不能直接更改代碼)既然不能更改代碼那隻能通過資料庫來處理了

--之前的sql方法，有可以會返回相同的類型則DECLARE @SNvarchar(2000)SET @S=‘‘SELECT @S=@S+‘/‘+(CASE ContactTypeWHEN ‘ContactType_1‘THEN ‘諮詢‘WHEN ‘ContactType_2‘THEN ‘回訪‘WHEN ‘ContactType_3‘THEN ‘隨訪‘WHEN ‘ContactType_4‘THEN ‘通知‘WHEN ‘ContactType_5‘THEN ‘預約‘WHEN ‘ContactType_6‘THEN ‘回複‘END)FROM test WHERE CID=81667PRINT @s

 

以下效果顯然不是我想要的  通過幾個小時的努力終於通過Sql完美解決，時間全部浪費在去重的問題上想到過的解決方案1.在之前Sql裡直接加入DISTINCT進行資料去重但一直出現錯誤"關鍵字 ‘DISTINCT‘ 附近有語法錯誤。 Severity 15"2.使用暫存資料表,先將資料查詢出來並插入暫存資料表，然後再迴圈暫存資料表並添加到字串,添加時如果存在則不會重複添加3.先按照原來的方法得到可能重複的字串，然後對字串進行去重處理4.使用我現在的方法即用for xml和DISTINCT得到已經去重的字串然後再賦予一個變數 FOR XML需要SqlServer 2005+版本支援

--新的Sql方法，通過FOR XML PATH和DISTINCT進行去重處理 DECLARE @SNvarchar(2000)--無法在包含DISTINCT關鍵字的查詢中將結果賦予變數,以下Sql將出現錯誤"關鍵字 ‘DISTINCT‘ 附近有語法錯誤。 Severity 15"-- SELECT @s= DISTINCT -- ( ‘/‘+(CASE ContactType -- WHEN ‘ContactType_1‘THEN ‘諮詢‘ -- WHEN ‘ContactType_2‘THEN ‘回訪‘ -- WHEN ‘ContactType_3‘THEN ‘隨訪‘ -- WHEN ‘ContactType_4‘THEN ‘通知‘ -- WHEN ‘ContactType_5‘THEN ‘預約‘ -- WHEN ‘ContactType_6‘THEN ‘回複‘ -- END) -- ) -- FROM test WHERE Cid=81667 -- FOR XML PATH(‘‘)SELECT @S=(SELECT DISTINCT (‘/‘+(CASE ContactTypeWHEN ‘ContactType_1‘THEN ‘諮詢‘WHEN ‘ContactType_2‘THEN ‘回訪‘WHEN ‘ContactType_3‘THEN ‘隨訪‘WHEN ‘ContactType_4‘THEN ‘通知‘WHEN ‘ContactType_5‘THEN ‘預約‘WHEN ‘ContactType_6‘THEN ‘回複‘END))FROM test WHERE Cid=81667FOR XML PATH(‘‘))PRINT @s

 

通過FOR XML和DISTINCT去重後的效果  可以看到使用for xml path可以很容易的將多選結果集轉換成一行 參考： 部落格園- 靈活運用 SQL SERVER FOR XML PATH MSDN 使用 FOR XML 構造 XML

來自為知筆記(Wiz)

使用SQL SERVER FOR XML PATH將多個結果集轉換成一行並進行去重處理