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