奇葩的需求

数据库开发

生成测试数据：

CREATE TABLE [dbo].[aa](
	[a] [nchar](10) NULL,
	[b] [nvarchar](50) NULL,
	[c] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT 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 colb
	FROM 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 NULL
	DROP TABLE tempdb..#tmp;
GO
CREATE 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 #tmp
	SELECT a.a,a.item,b.item
	FROM t_name a INNER JOIN t_job b ON a.rn = b.rn

DECLARE @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)