字符/字段数据的合并

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

Figure-1: 打卡记录

插入测试数据：

IF OBJECT_ID(N'dbo.CARD_RECORD','U') IS NOT NULLBEGIN        DROP TABLE dbo.CARD_RECORD;ENDGOCREATE TABLE dbo.CARD_RECORD (    id_ INT IDENTITY(1,1) NOT NULL PRIMARY KEY,    card_id INT NOT NULL,    swipe_date DATETIME NOT NULL)GOINSERT INTO dbo.CARD_RECORD (card_id,swipe_date)SELECT '10001','2015-06-01 08:21'UNION ALLSELECT '10002','2015-06-01 08:22'UNION ALLSELECT '10001','2015-06-01 12:00'UNION ALLSELECT '10002','2015-06-01 12:01'UNION ALLSELECT '10001','2015-06-01 13:00'UNION ALLSELECT '10002','2015-06-01 13:01'UNION ALLSELECT '10002','2015-06-01 18:05'UNION ALLSELECT '10001','2015-06-01 18:12'UNION ALLSELECT '10002','2015-06-02 08:31'UNION ALLSELECT '10001','2015-06-02 08:42'UNION ALLSELECT '10001','2015-06-02 12:10'UNION ALLSELECT '10002','2015-06-02 12:11'UNION ALLSELECT '10001','2015-06-02 13:00'UNION ALLSELECT '10002','2015-06-02 13:11'UNION ALLSELECT '10001','2015-06-02 18:05'UNION ALLSELECT '10002','2015-06-02 18:12'UNION ALLSELECT '10002','2015-06-02 19:34'UNION ALLSELECT '10001','2015-06-03 08:36'UNION ALLSELECT '10002','2015-06-03 08:40'UNION ALLSELECT '10001','2015-06-03 12:20'UNION ALLSELECT '10002','2015-06-03 12:20'UNION ALLSELECT '10001','2015-06-03 12:55'UNION ALLSELECT '10002','2015-06-03 12:56'UNION ALLSELECT '10001','2015-06-03 18:05'GO

Code-1: 插入测试数据

创建字符/字段数据合并的函数：

IF OBJECT_ID(N'dbo.fn_time_list') IS NOT NULLBEGIN    DROP FUNCTION dbo.fn_time_listENDGOCREATE 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)ENDGO

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 time10FROM CTE4GROUP BY card_id,swipe_dateORDER BY card_id ASC,swipe_date ASC;GO

Code-4: 转为每个时间占用一个字段

最终的效果：

Figure-3: 最终显示的效果