字符/字段数据的合并

前两篇文章是关于字符串分割的一些经验，今天来谈下字符的合并，更确切的说是字段数据的合并吧。现在很多公司，尤其是工厂，上下班都要刷卡。假如有以下的数据库表，记录着员工上下班的刷卡记录。但是这样直接导出或显示，不太好看，不够直观。下面实现把每个员工每天的刷卡时间横向显示。

插入测试数据：

 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 ALL11 SELECT '10002','2015-06-01 08:22'12 UNION ALL13 SELECT '10001','2015-06-01 12:00'14 UNION ALL15 SELECT '10002','2015-06-01 12:01'16 UNION ALL17 SELECT '10001','2015-06-01 13:00'18 UNION ALL19 SELECT '10002','2015-06-01 13:01'20 UNION ALL21 SELECT '10002','2015-06-01 18:05'22 UNION ALL23 SELECT '10001','2015-06-01 18:12'24 UNION ALL25 SELECT '10002','2015-06-02 08:31'26 UNION ALL27 SELECT '10001','2015-06-02 08:42'28 UNION ALL29 SELECT '10001','2015-06-02 12:10'30 UNION ALL31 SELECT '10002','2015-06-02 12:11'32 UNION ALL33 SELECT '10001','2015-06-02 13:00'34 UNION ALL35 SELECT '10002','2015-06-02 13:11'36 UNION ALL37 SELECT '10001','2015-06-02 18:05'38 UNION ALL39 SELECT '10002','2015-06-02 18:12'40 UNION ALL41 SELECT '10002','2015-06-02 19:34'42 UNION ALL43 SELECT '10001','2015-06-03 08:36'44 UNION ALL45 SELECT '10002','2015-06-03 08:40'46 UNION ALL47 SELECT '10001','2015-06-03 12:20'48 UNION ALL49 SELECT '10002','2015-06-03 12:20'50 UNION ALL51 SELECT '10001','2015-06-03 12:55'52 UNION ALL53 SELECT '10002','2015-06-03 12:56'54 UNION ALL55 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 BEGIN11     DECLARE @char NVARCHAR(MAX)12     DECLARE @date2 DATE13     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_id19     ORDER BY swipe_date ASC20     21     SET @char = CASE WHEN @char = '' THEN '' ELSE SUBSTRING(@char,1,LEN(@char)-1) END22     23     RETURN (@char)24 END25 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_list2 FROM CARD_RECORD 3 ORDER BY card_id ASC,swipe_date ASC4 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_no12     ,card_id13     ,swipe_date14     ,swipe_time15     FROM CTE116 )17 18 SELECT 19  t1.card_id20 ,t1.swipe_date21 ,t1.swipe_time AS time122 ,ISNULL(t2.swipe_time,'') AS time223 ,ISNULL(t3.swipe_time,'') AS time324 ,ISNULL(t4.swipe_time,'') AS time425 ,ISNULL(t5.swipe_time,'') AS time526 ,ISNULL(t6.swipe_time,'') AS time627 ,ISNULL(t7.swipe_time,'') AS time728 ,ISNULL(t8.swipe_time,'') AS time829 ,ISNULL(t9.swipe_time,'') AS time930 ,ISNULL(t10.swipe_time,'') AS time1031 32 FROM CTE2 AS t133 LEFT JOIN CTE2 AS t2 ON t2.card_id = t1.card_id AND t2.swipe_date = t1.swipe_date AND t2.row_no = 234 LEFT JOIN CTE2 AS t3 ON t3.card_id = t1.card_id AND t3.swipe_date = t1.swipe_date AND t3.row_no = 335 LEFT JOIN CTE2 AS t4 ON t4.card_id = t1.card_id AND t4.swipe_date = t1.swipe_date AND t4.row_no = 436 LEFT JOIN CTE2 AS t5 ON t5.card_id = t1.card_id AND t5.swipe_date = t1.swipe_date AND t5.row_no = 537 LEFT JOIN CTE2 AS t6 ON t6.card_id = t1.card_id AND t6.swipe_date = t1.swipe_date AND t6.row_no = 638 LEFT JOIN CTE2 AS t7 ON t7.card_id = t1.card_id AND t7.swipe_date = t1.swipe_date AND t7.row_no = 739 LEFT JOIN CTE2 AS t8 ON t8.card_id = t1.card_id AND t8.swipe_date = t1.swipe_date AND t8.row_no = 840 LEFT JOIN CTE2 AS t9 ON t9.card_id = t1.card_id AND t9.swipe_date = t1.swipe_date AND t9.row_no = 941 LEFT JOIN CTE2 AS t10 ON t10.card_id = t1.card_id AND t10.swipe_date = t1.swipe_date AND t10.row_no = 1042 43 WHERE t1.row_no = 144 ORDER BY t1.card_id ASC,t1.swipe_date ASC45 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_no12     ,card_id13     ,swipe_date14     ,swipe_time15     FROM CTE316 )17 18 SELECT 19  card_id20 ,swipe_date21 ,MAX(CASE WHEN row_no = 1 THEN swipe_time ELSE '' END) AS time122 ,MAX(CASE WHEN row_no = 2 THEN swipe_time ELSE '' END) AS time223 ,MAX(CASE WHEN row_no = 3 THEN swipe_time ELSE '' END) AS time324 ,MAX(CASE WHEN row_no = 4 THEN swipe_time ELSE '' END) AS time425 ,MAX(CASE WHEN row_no = 5 THEN swipe_time ELSE '' END) AS time526 ,MAX(CASE WHEN row_no = 6 THEN swipe_time ELSE '' END) AS time627 ,MAX(CASE WHEN row_no = 7 THEN swipe_time ELSE '' END) AS time728 ,MAX(CASE WHEN row_no = 8 THEN swipe_time ELSE '' END) AS time829 ,MAX(CASE WHEN row_no = 9 THEN swipe_time ELSE '' END) AS time930 ,MAX(CASE WHEN row_no = 10 THEN swipe_time ELSE '' END) AS time1031 32 FROM CTE433 GROUP BY card_id,swipe_date34 ORDER BY card_id ASC,swipe_date ASC35 36 GO