前两篇文章是关于字符串分割的一些经验，今天来谈下字符的合并，更确切的说是字段数据的合并吧。现在很多公司，尤其是工厂，上下班都要刷卡。假如有以下的数据库表，记录着员工上下班的刷卡记录。但是这样直接导出或显示，不太好看，不够直观。下面实现把每个员工每天的刷卡时间横向显示。
插入测试数据：
1 CREATE TABLE CARD_RECORD ( 2 id_ INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 3 card_id INT NOT NULL, 4 swipe_date DATETIME NOT NULL 5 ) 6 GO 7 8 INSERT INTO CARD_RECORD (card_id,swipe_date) 9 SELECT '10001','2015-06-01 08:21' 10 UNION ALL 11 SELECT '10002','2015-06-01 08:22' 12 UNION ALL 13 SELECT '10001','2015-06-01 12:00' 14 UNION ALL 15 SELECT '10002','2015-06-01 12:01' 16 UNION ALL 17 SELECT '10001','2015-06-01 13:00' 18 UNION ALL 19 SELECT '10002','2015-06-01 13:01' 20 UNION ALL 21 SELECT '10002','2015-06-01 18:05' 22 UNION ALL 23 SELECT '10001','2015-06-01 18:12' 24 UNION ALL 25 SELECT '10002','2015-06-02 08:31' 26 UNION ALL 27 SELECT '10001','2015-06-02 08:42' 28 UNION ALL 29 SELECT '10001','2015-06-02 12:10' 30 UNION ALL 31 SELECT '10002','2015-06-02 12:11' 32 UNION ALL 33 SELECT '10001','2015-06-02 13:00' 34 UNION ALL 35 SELECT '10002','2015-06-02 13:11' 36 UNION ALL 37 SELECT '10001','2015-06-02 18:05' 38 UNION ALL 39 SELECT '10002','2015-06-02 18:12' 40 UNION ALL 41 SELECT '10002','2015-06-02 19:34' 42 UNION ALL 43 SELECT '10001','2015-06-03 08:36' 44 UNION ALL 45 SELECT '10002','2015-06-03 08:40' 46 UNION ALL 47 SELECT '10001','2015-06-03 12:20' 48 UNION ALL 49 SELECT '10002','2015-06-03 12:20' 50 UNION ALL 51 SELECT '10001','2015-06-03 12:55' 52 UNION ALL 53 SELECT '10002','2015-06-03 12:56' 54 UNION ALL 55 SELECT '10001','2015-06-03 18:05' 56 GO
创建字符/字段数据合并的函数：
1 IF OBJECT_ID(N'fn_time_list') IS NOT NULL 2 BEGIN 3 DROP FUNCTION fn_time_list 4 END 5 GO 6 7 CREATE FUNCTION fn_time_list (@date DATETIME,@card_id NVARCHAR(100)) 8 RETURNS NVARCHAR(MAX) 9 AS 10 BEGIN 11 DECLARE @char NVARCHAR(MAX) 12 DECLARE @date2 DATE 13 SET @char = '' 14 SET @date = CAST(@date AS DATE) 15 SET @date2 = DATEADD(DAY,1,CAST(@date AS DATE)) 16 17 SELECT @char = @char + CONVERT(CHAR(5),swipe_date,108) + ',' FROM CARD_RECORD 18 WHERE (swipe_date >= @date AND swipe_date < @date2) AND card_id=@card_id 19 ORDER BY swipe_date ASC 20 21 SET @char = CASE WHEN @char = '' THEN '' ELSE SUBSTRING(@char,1,LEN(@char)-1) END 22 23 RETURN (@char) 24 END 25 GO
最后，把数据转换一下，展示：
1 SELECT DISTINCT card_id,CONVERT(char(10),swipe_date,23) AS swipe_date,dbo.fn_time_list(swipe_date,card_id) AS time_list 2 FROM CARD_RECORD 3 ORDER BY card_id ASC,swipe_date ASC 4 GO
执行结果：
这里，还可以把竖向的刷卡时间，每个时间占一个字段，横向显示。不过会有刷卡次数的限制，一般来说，一天预留十次刷卡记录应该是足够了。
1 WITH CTE1 AS ( 2 SELECT 3 card_id 4 ,CONVERT(CHAR(10),swipe_date,23) AS swipe_date 5 ,CONVERT(CHAR(5),swipe_date,108) AS swipe_time 6 FROM CARD_RECORD 7 ) 8 9 ,CTE2 AS ( 10 SELECT 11 ROW_NUMBER() OVER (PARTITION BY card_id,swipe_date ORDER BY card_id ASC,swipe_date ASC,swipe_time ASC) AS row_no 12 ,card_id 13 ,swipe_date 14 ,swipe_time 15 FROM CTE1 16 ) 17 18 SELECT 19 t1.card_id 20 ,t1.swipe_date 21 ,t1.swipe_time AS time1 22 ,ISNULL(t2.swipe_time,'') AS time2 23 ,ISNULL(t3.swipe_time,'') AS time3 24 ,ISNULL(t4.swipe_time,'') AS time4 25 ,ISNULL(t5.swipe_time,'') AS time5 26 ,ISNULL(t6.swipe_time,'') AS time6 27 ,ISNULL(t7.swipe_time,'') AS time7 28 ,ISNULL(t8.swipe_time,'') AS time8 29 ,ISNULL(t9.swipe_time,'') AS time9 30 ,ISNULL(t10.swipe_time,'') AS time10 31 32 FROM CTE2 AS t1 33 LEFT JOIN CTE2 AS t2 ON t2.card_id = t1.card_id AND t2.swipe_date = t1.swipe_date AND t2.row_no = 2 34 LEFT JOIN CTE2 AS t3 ON t3.card_id = t1.card_id AND t3.swipe_date = t1.swipe_date AND t3.row_no = 3 35 LEFT JOIN CTE2 AS t4 ON t4.card_id = t1.card_id AND t4.swipe_date = t1.swipe_date AND t4.row_no = 4 36 LEFT JOIN CTE2 AS t5 ON t5.card_id = t1.card_id AND t5.swipe_date = t1.swipe_date AND t5.row_no = 5 37 LEFT JOIN CTE2 AS t6 ON t6.card_id = t1.card_id AND t6.swipe_date = t1.swipe_date AND t6.row_no = 6 38 LEFT JOIN CTE2 AS t7 ON t7.card_id = t1.card_id AND t7.swipe_date = t1.swipe_date AND t7.row_no = 7 39 LEFT JOIN CTE2 AS t8 ON t8.card_id = t1.card_id AND t8.swipe_date = t1.swipe_date AND t8.row_no = 8 40 LEFT JOIN CTE2 AS t9 ON t9.card_id = t1.card_id AND t9.swipe_date = t1.swipe_date AND t9.row_no = 9 41 LEFT JOIN CTE2 AS t10 ON t10.card_id = t1.card_id AND t10.swipe_date = t1.swipe_date AND t10.row_no = 10 42 43 WHERE t1.row_no = 1 44 ORDER BY t1.card_id ASC,t1.swipe_date ASC 45 GO
最后的效果：
======= 以下代码减少了CTE虚拟表LEFT JOIN的次数，效率更高。修改于2015-06-24 19:22 ===========
1 ;WITH CTE3 AS ( 2 SELECT 3 card_id 4 ,CONVERT(CHAR(10),swipe_date,23) AS swipe_date 5 ,CONVERT(CHAR(5),swipe_date,108) AS swipe_time 6 FROM CARD_RECORD 7 ) 8 9 ,CTE4 AS ( 10 SELECT 11 ROW_NUMBER() OVER (PARTITION BY card_id,swipe_date ORDER BY card_id ASC,swipe_date ASC,swipe_time ASC) AS row_no 12 ,card_id 13 ,swipe_date 14 ,swipe_time 15 FROM CTE3 16 ) 17 18 SELECT 19 card_id 20 ,swipe_date 21 ,MAX(CASE WHEN row_no = 1 THEN swipe_time ELSE '' END) AS time1 22 ,MAX(CASE WHEN row_no = 2 THEN swipe_time ELSE '' END) AS time2 23 ,MAX(CASE WHEN row_no = 3 THEN swipe_time ELSE '' END) AS time3 24 ,MAX(CASE WHEN row_no = 4 THEN swipe_time ELSE '' END) AS time4 25 ,MAX(CASE WHEN row_no = 5 THEN swipe_time ELSE '' END) AS time5 26 ,MAX(CASE WHEN row_no = 6 THEN swipe_time ELSE '' END) AS time6 27 ,MAX(CASE WHEN row_no = 7 THEN swipe_time ELSE '' END) AS time7 28 ,MAX(CASE WHEN row_no = 8 THEN swipe_time ELSE '' END) AS time8 29 ,MAX(CASE WHEN row_no = 9 THEN swipe_time ELSE '' END) AS time9 30 ,MAX(CASE WHEN row_no = 10 THEN swipe_time ELSE '' END) AS time10 31 32 FROM CTE4 33 GROUP BY card_id,swipe_date 34 ORDER BY card_id ASC,swipe_date ASC 35 36 GO