日期:2014-05-17 浏览次数:20602 次
--> 测试数据:#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([name] VARCHAR(19))
INSERT #tb
SELECT '+6LC+YOP+Y3E' UNION ALL
SELECT '++6LC+TDF+Kaletra' UNION ALL
SELECT '+EFV+6LC+Y3E' UNION ALL
SELECT '6LC+BGH+Atripla' UNION ALL
SELECT '6LC+BGH+Y3E' UNION ALL
SELECT '+++6LC+BGH+Y3E+' UNION ALL
SELECT '+6LC+BGH+克芝' UNION ALL
SELECT '6LC+YOP++' UNION ALL
SELECT 'DDI+克芝+6LC+BGH' UNION ALL
SELECT 'EFV+6LC++双汰芝' UNION ALL
SELECT 'EFV+Y3E+Eziom' UNION ALL
SELECT 'EFV+Y3E+YOP+6LC+BGH' UNION ALL
SELECT 'EFV++利韦+克芝' UNION ALL
SELECT 'YOP' UNION ALL
SELECT 'YOP+++' UNION ALL
SELECT '克芝' UNION ALL
SELECT '克芝+6LC+利韦' UNION ALL
SELECT '双汰芝+++' UNION ALL
SELECT '双汰芝+Kaletra++'
--------------开始查询--------------------------
;WITH cte AS
(
SELECT name, [values] = C.v.value('.' , 'nvarchar(100)'),row_id=ROW_NUMBER()OVER(PARTITION BY [name] ORDER BY [name])
FROM (
SELECT [name],[values]= CONVERT( XML, '<root><v>'+ REPLACE ([name] , '+' , '</v><v>')+ '</v></root>').query('.') FROM #tb
) a
OUTER APPLY a.[values].nodes('/root/v') C (v)
WHERE C.v.value('.' , 'nvarchar(100)') <> ''
)
SELECT * FROM cte PIVOT(MAX([values]) FOR row_id IN([1],[2],[3],[4],[5],[6],[7])) pvt
----------------结果----------------------------
/*
name 1 2 3 4 5 6 7
+++6LC+BGH+Y3E+ 6LC BGH Y3E NULL NULL NULL NULL
++6LC+TDF+Kaletra 6LC TDF Kaletra NULL NULL NULL NULL
+6LC+BGH+克芝 6LC BGH 克芝 NULL NULL NULL NULL
+6LC+YOP+Y3E 6LC YOP Y3E NULL NULL NULL NULL
+EFV+6LC+Y3E EFV 6LC Y3E NULL NULL NULL NULL
6LC+BGH+Atripla 6LC BGH Atripla NULL NULL NULL NULL
6LC+BGH+Y3E 6LC BGH Y3E NULL NULL NULL NULL
6LC+YOP++ 6LC YOP NULL NULL NULL NULL NULL
DDI+克芝+6LC+BGH DDI 克芝 6LC BGH NULL NULL NULL
EFV++利韦+克芝 EFV 利韦 克芝 NULL NULL NULL NULL
EFV+6LC++双汰芝 EFV 6LC 双汰芝 NULL NULL NULL NULL
EFV+Y3E+Eziom EFV Y3E Eziom NULL NULL NULL NULL
EFV+Y3E+YOP+6LC+BGH EFV Y3E YOP 6LC BGH NULL NULL
YOP YOP NULL NULL NULL NULL NULL NULL
YOP+++ YOP NULL NULL NULL NULL NULL NULL
克芝 克芝 NULL NULL NULL NULL NULL NULL
克芝+6LC+利韦 克芝 6LC 利韦 NULL NULL NULL NULL
双汰芝+++ 双汰芝 NULL NULL NULL NULL NULL NULL
双汰芝+Kaletra++ 双汰芝 Kaletra NULL NULL NULL NULL NULL
*/