189 8069 5689
字符的合并，更确切的说是字段数据的合并吧。现在很多公司，尤其是工厂，上下班都要刷卡。假如有以下的数据库表，记录着员工上下班的刷卡记录。但是这样直接导出或显示，不太好看，不够直观。下面实现把每个员工每天的刷卡时间横向显示。
十年的密云网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。网络营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整密云建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联从事“密云网站设计”,“密云网站推广”以来，每个客户项目都认真落实执行。
Figure-1: 打卡记录
插入测试数据：
IF OBJECT_ID(N'dbo.CARD_RECORD','U') IS NOT NULL BEGIN DROP TABLE dbo.CARD_RECORD; END GO CREATE TABLE dbo.CARD_RECORD ( id_ INT IDENTITY(1,1) NOT NULL PRIMARY KEY, card_id INT NOT NULL, swipe_date DATETIME NOT NULL ) GO INSERT INTO dbo.CARD_RECORD (card_id,swipe_date) SELECT '10001','2015-06-01 08:21' UNION ALL SELECT '10002','2015-06-01 08:22' UNION ALL SELECT '10001','2015-06-01 12:00' UNION ALL SELECT '10002','2015-06-01 12:01' UNION ALL SELECT '10001','2015-06-01 13:00' UNION ALL SELECT '10002','2015-06-01 13:01' UNION ALL SELECT '10002','2015-06-01 18:05' UNION ALL SELECT '10001','2015-06-01 18:12' UNION ALL SELECT '10002','2015-06-02 08:31' UNION ALL SELECT '10001','2015-06-02 08:42' UNION ALL SELECT '10001','2015-06-02 12:10' UNION ALL SELECT '10002','2015-06-02 12:11' UNION ALL SELECT '10001','2015-06-02 13:00' UNION ALL SELECT '10002','2015-06-02 13:11' UNION ALL SELECT '10001','2015-06-02 18:05' UNION ALL SELECT '10002','2015-06-02 18:12' UNION ALL SELECT '10002','2015-06-02 19:34' UNION ALL SELECT '10001','2015-06-03 08:36' UNION ALL SELECT '10002','2015-06-03 08:40' UNION ALL SELECT '10001','2015-06-03 12:20' UNION ALL SELECT '10002','2015-06-03 12:20' UNION ALL SELECT '10001','2015-06-03 12:55' UNION ALL SELECT '10002','2015-06-03 12:56' UNION ALL SELECT '10001','2015-06-03 18:05' GO
Code-1: 插入测试数据
创建字符/字段数据合并的函数：
IF OBJECT_ID(N'dbo.fn_time_list') IS NOT NULL BEGIN DROP FUNCTION dbo.fn_time_list END GO CREATE FUNCTION dbo.fn_time_list ( @date DATETIME, @card_id NVARCHAR(100) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @char NVARCHAR(MAX) DECLARE @date2 DATE SET @char = '' SET @date = CAST(@date AS DATE) SET @date2 = DATEADD(DAY,1,CAST(@date AS DATE)) SELECT @char = @char + CONVERT(CHAR(5),swipe_date,108) + ',' FROM CARD_RECORD WHERE (swipe_date >= @date AND swipe_date < @date2) AND card_id=@card_id ORDER BY swipe_date ASC SET @char = CASE WHEN @char = '' THEN '' ELSE SUBSTRING(@char,1,LEN(@char)-1) END RETURN (@char) END GO
Code-2: 字符/字段数据合并函数
最后，把数据转换一下，展示：
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 dbo.CARD_RECORD ORDER BY card_id ASC,swipe_date ASC; GO
Code-3: 转换显示
执行结果：
Figure-2: 转换后显示的效果
这里，还可以把竖向的刷卡时间，每个时间占一个字段，横向显示，方便统计等。不过会有刷卡次数的限制，一般来说，一天预留十次刷卡记录应该是足够了。
WITH CTE3 AS ( SELECT card_id ,CONVERT(CHAR(10),swipe_date,23) AS swipe_date ,CONVERT(CHAR(5),swipe_date,108) AS swipe_time FROM CARD_RECORD ) ,CTE4 AS ( SELECT ROW_NUMBER() OVER (PARTITION BY card_id,swipe_date ORDER BY card_id ASC,swipe_date ASC,swipe_time ASC) AS row_no ,card_id ,swipe_date ,swipe_time FROM CTE3 ) SELECT card_id ,swipe_date ,MAX(CASE WHEN row_no = 1 THEN swipe_time ELSE '' END) AS time1 ,MAX(CASE WHEN row_no = 2 THEN swipe_time ELSE '' END) AS time2 ,MAX(CASE WHEN row_no = 3 THEN swipe_time ELSE '' END) AS time3 ,MAX(CASE WHEN row_no = 4 THEN swipe_time ELSE '' END) AS time4 ,MAX(CASE WHEN row_no = 5 THEN swipe_time ELSE '' END) AS time5 ,MAX(CASE WHEN row_no = 6 THEN swipe_time ELSE '' END) AS time6 ,MAX(CASE WHEN row_no = 7 THEN swipe_time ELSE '' END) AS time7 ,MAX(CASE WHEN row_no = 8 THEN swipe_time ELSE '' END) AS time8 ,MAX(CASE WHEN row_no = 9 THEN swipe_time ELSE '' END) AS time9 ,MAX(CASE WHEN row_no = 10 THEN swipe_time ELSE '' END) AS time10 FROM CTE4 GROUP BY card_id,swipe_date ORDER BY card_id ASC,swipe_date ASC; GO
Code-4: 转为每个时间占用一个字段
最终的效果：
Figure-3: 最终显示的效果