奇葩的需求

生成測試數據：

CREATE TABLE [dbo].[aa]([a] [nchar](10) NULL,[b] [nvarchar](50) NULL,[c] [nvarchar](50) NULL) ON [PRIMARY]GOINSERT INTO aa VALUES ('a','杨静,马丽萍','美容师,美体师')INSERT INTO aa VALUES ('b','杨静,马丽萍,时晶晶','美容师,美体师,顾问')GO

需求：

變成：

咋一看真的會感覺無從下手，但有句話說得好：當你要釘釘子的時候，你所看到的一切都是錘子。

所以我馬上搜索已有的知識，發現用 拆分  + cross apply +  行轉列 可以實現上述需求，而這三個知識點，我之前剛好有用心學習過，剛好可以當做我的錘子。

思路：先將字符串分拆成列表後與第一列交叉連接，變成中間結果，再行轉列。

先給出靜態代碼（代碼中 f_split 為拆分函數）：

;WITH t_name AS( SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) rn,*FROM (SELECT a FROM aa) s CROSS APPLY dbo.f_split((SELECT b FROM aa WHERE a = s.a),',') t),t_job AS(SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) rn,* FROM (SELECT a FROM aa) s CROSS APPLY dbo.f_split((SELECT c FROM aa WHERE a = s.a),',') t),t_name_job AS(SELECT a.a 工号,a.item cola,b.item colbFROM t_name a INNER JOIN t_job b ON a.rn = b.rn)SELECT *FROM t_name_job PIVOT(MAX(cola) FOR colb IN(美容师,美体师,顾问)) a

 也寫了一個類似的動態版本，但有些缺點，不能控制列標題的出現順序，它自動排序了。但好處還是有的，動態的就不用管具體有哪些值了，比如加一行後的需求：

變成：

下述代碼也可以一樣實現：

IF OBJECT_ID('tempdb..#tmp') IS NOT NULLDROP TABLE tempdb..#tmp;GOCREATE TABLE #tmp(col1 VARCHAR(10),col2 NVARCHAR(25),col3 NVARCHAR(25))GO;WITH t_name AS( SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) rn,*FROM (SELECT a FROM aa) s CROSS APPLY dbo.f_split((SELECT b FROM aa WHERE a = s.a),',') t),t_job AS(SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) rn,* FROM (SELECT a FROM aa) s CROSS APPLY dbo.f_split((SELECT c FROM aa WHERE a = s.a),',') t)INSERT INTO #tmpSELECT a.a,a.item,b.itemFROM t_name a INNER JOIN t_job b ON a.rn = b.rnDECLARE @sql VARCHAR(2000),@var_str VARCHAR(1000)SET @var_str = STUFF((SELECT DISTINCT ','+col3 FROM #tmp FOR XML PATH('')),1,1,'')SET @sql = 'select col1 工号,' + @var_str +    ' from #tmp pivot(max(col2) for col3 in(' + @var_str + ')) a'EXEC(@sql)