Shulou(Shulou.com)06/01 Report--

The merging of characters, or rather the merging of field data. Nowadays, many companies, especially factories, have to pay by card to and from work. If you have the following database table, record the credit card records of employees commuting to and from work. But this direct export or display, not very good-looking, not intuitive. The following is to realize the horizontal display of the daily swiping time of each employee.

Figure-1: sign-in record

Insert test data:

IF OBJECT_ID (Numbdbo.CARDRECORDINGRECORDU') IS NOT NULLBEGIN DROP TABLE dbo.CARD_RECORD ENDGOCREATE TABLE dbo.CARD_RECORD (id_ INT IDENTITY (1pyr1) NOT NULL PRIMARY KEY, card_id INT NOT NULL, swipe_date DATETIME NOT NULL) GOINSERT INTO dbo.CARD_RECORD (card_id,swipe_date) SELECT '10001ZhenZhe 08:22'UNION ALLSELECT' 10001ZZZZZZOOO6-01-01 08:22'UNION ALLSELECT '10001ZHI-2015-06-01 12:01'UNION ALLSELECT' 10001' 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:11'UNION ALLSELECT '10001 '2015-06-02 13:00'UNION ALLSELECT' 10002 19:34'UNION ALLSELECT '10002 19:34'UNION ALLSELECT' 10001 12:20'UNION ALLSELECT '10001 12:20'UNION ALLSELECT' 10002 '2015-06-03 12:20'UNION ALLSELECT' 10001 18:05'GO 2015-06-03 12:55'UNION ALLSELECT '10002 18:05'GO

Code-1: inserting test data

A function to create a character / field data merge:

IF OBJECT_ID (date DATETIME, @ card_id NVARCHAR) 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: character / field data merge function

Finally, convert the data and show:

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: converting display

Execution result:

Figure-2: the effect displayed after conversion

Here, you can also put the vertical credit card time, each time occupies a field, horizontal display, convenient statistics and so on. However, there will be a limit on the number of times you swipe your credit card. Generally speaking, it should be enough to reserve a record of ten times a day.

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: switch to one field per time

The final effect:

Figure-3: the final effect displayed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.