SQL Server数据转换 [英] SQL Server Data Conversion

问题描述

我的数据格式为:

Date, FirstName, LastName, Unit
Jan1 , Bob      , Guy     , Home
Jan2 , Bob      , Guy     , Home
Jan3 , Bob      , Guy     , Home
Jan5 , Bob      , Guy     , Home
Jan6 , Bob      , Guy     , Home
Jan7 , Bob      , Guy     , Home
Jan8 , Bob      , Guy     , Offsite
Jan3 , Jane     , Doe     , Home
Jan4 , Jane     , Doe     , Home
Jan5 , Jane     , Doe     , Home
Jan9 , Bob      , Guy     , Home
Jan10, Bob      , Guy     , Home
Jan11, Bob      , Guy     , Home
Jan12, Jane     , Doe     , Home
Jan13, Jane     , Doe     , Home
Jan14, Jane     , Doe     , Home

我想要它的格式

DateStart, DateEnd, FirstName, LastName, Unit
Jan1     , Jan3   , Bob      , Guy     , Home
Jan5     , Jan7   , Bob      , Guy     , Home
Jan8     , Jan8   , Bob      , Guy     , Offsite
Jan3     , Jan5   , Jane     , Doe     , Home
Jan9     , Jan11  , Bob      , Guy     , Home
Jan12    , Jan14  , Jane     , Doe     , Home

更新了数据.

如何轻松转换数据?

这是一次转换.

感谢您的评论/回答！

推荐答案

下面的SQL将产生所需的输出，但是我不确定您是否最好将其编写为C#

The SQL below will produce the desired output, But I'm not sure if you're not better off writing this is C#

更新这已更新为适当的差距和孤岛解决方案.这是基于MSDN文章序列号中的孤岛和空白.这可以通过使用CTE进行改进，也可以用LEFT JOIN代替Exists.

Update This has been updated to a proper gaps and island solution. This is based on the MSDN article Islands and Gaps in Sequential Numbers by Alexander Kozak. This could be improved on by using CTE's and also the Exists could be replaced with LEFT JOINs.

应注意，这依赖于日期，没有任何时间部分.如果有时间成分，则必须提前将其删除.

It should be noted that this relies on the Dates to not have any time component. If there were a time component it would have to be removed up front.

输出

Date                    enddate                 FirstName LastName Unit
----------------------- ----------------------- --------- -------- -------
2011-01-01 00:00:00.000 2011-01-03 00:00:00.000 Bob       Guy      Home
2011-01-03 00:00:00.000 2011-01-05 00:00:00.000 Jane      Doe      Home
2011-01-05 00:00:00.000 2011-01-07 00:00:00.000 Bob       Guy      Home
2011-01-08 00:00:00.000 2011-01-08 00:00:00.000 Bob       Guy      Offsite
2011-01-09 00:00:00.000 2011-01-11 00:00:00.000 Bob       Guy      Home
2011-01-12 00:00:00.000 2011-01-14 00:00:00.000 Jane      Doe      Home

SQL语句

SET NOCOUNT On

DECLARE @Test 
Table (
    Date datetime, 
    FirstName varchar(100),
     LastName varchar(100), 
     Unit  varchar(7))



INSERT INTO @Test VALUES ('01/01/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/02/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/03/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/05/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/06/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/07/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/08/2011'  , 'Bob', 'Guy',  'Offsite')
INSERT INTO @Test VALUES ('01/03/2011'  , 'Jane', 'Doe',  'Home')
INSERT INTO @Test VALUES ('01/04/2011'  , 'Jane', 'Doe',  'Home')
INSERT INTO @Test VALUES ('01/05/2011'  , 'Jane', 'Doe',  'Home')
INSERT INTO @Test VALUES ('01/09/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/10/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/11/2011'  , 'Bob', 'Guy',  'Home')
INSERT INTO @Test VALUES ('01/12/2011'  , 'Jane', 'Doe',  'Home')
INSERT INTO @Test VALUES ('01/13/2011'  , 'Jane', 'Doe',  'Home')
INSERT INTO @Test VALUES ('01/14/2011'  , 'Jane', 'Doe',  'Home')


SELECT 
    t1.Date,
    MIN(t2.Date) enddate, 
    t1.FirstName,
    t1.LastName,
    t1.Unit
 FROM

    (SELECT * 
    FROM
        @Test t1
    WHERE
       NOT EXISTS(SELECT * FROM @Test t2
                  WHERE
                      t1.firstName = t2.FirstName   
                      AND t1.LastName = t2.LastName
                      AND t1.Unit = t2.Unit
                        and t1.Date - t2.Date = 1)) 
        t1


      INNER JOIN (SELECT * FROM @Test t1

    WHERE
       NOT EXISTS(SELECT * FROM @Test t2
                  WHERE
                      t1.firstName = t2.FirstName   
                      AND t1.LastName = t2.LastName
                      AND t1.Unit = t2.Unit
                        and t2.Date - t1.Date = 1)) t2
      ON
        t1.firstName = t2.FirstName 
                      AND t1.LastName = t2.LastName
                      AND t1.Unit = t2.Unit
                      AND t1.Date <= t2.Date  
       GROUP BY
       t1.Date,
       t1.FirstName,
       t1.LastName,
       t1.Unit

这篇关于SQL Server数据转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！