同一行和不同列的多个行值

I have a table with 4 fields Person_id, Store_id, startdate and enddate. For a particular value for person_id, there can be a number of records with different start and end dates. Here I need to update the multiple row values to same row and different columns.

我有一个包含4个字段的表:Person_id、Store_id、startdate和enddate。对于person_id的特定值，可以有许多不同的开始和结束日期的记录。这里我需要将多行值更新为相同的行和不同的列。

Example 1:

示例1:

Person_id        Store_ID         Startdate       enddate
10000351067      10000232561      2016-09-09      2016-09-16
10000351067      10000232561      2016-09-16      2016-10-03
10000351067      10000232561      2016-10-03      2016-10-07
10000351067      10000232561      2016-10-07      2017-01-17
10000351067      10000232561      2017-01-17      2018-04-05
10000351067      10000232561      2018-04-05      NULL

Example 2:

示例2:

10000193858      10000225875      2016-07-13          2016-08-03
10000193858      10000225875      2016-08-03          2017-05-17
10000193858      10000225875      2017-05-17          2017-06-05
10000193858      10000225875      2017-05-31          2017-06-05
10000193858      10000225875      2017-06-05          2017-06-13
10000193858      10000225875      2017-06-13          2017-08-16
10000193858      10000225875      2017-08-07          2017-08-16
10000193858      10000225875      2017-08-16          2017-08-18
10000193858      10000225875      2017-08-18          2017-08-31
10000193858      10000225875      2017-08-31          2018-01-05
10000193858      10000225875      2018-01-05          NULL

Result set:

结果集:

For example1:

例二:

Person_id   store_id      Start_date_1 Ended_at_1  Started_at_2 Ended_at_2...
10000351067 10000232561   2016-09-09   2016-09-16  2016-09-16   2016-10-03...

For example2:

example2:

Person_id    store_id     Start_date_1 Ended_at_1  Started_at_2 Ended_at_2...
10000193858  10000225875  2016-07-13   2016-08-03  2016-08-03   2017-05-17....

2 个解决方案

#1

This is a somewhat convoluted issue, but can be resolved with a CROSS APPLY to unpivot data, followed by a PIVOT to get it back in the format you need. It was tested on SQL 2017, but I believe PIVOT and CTEs were added in 2005 and ROW_NUMBER() in 2008, so should work in your version of SQL. In the main query, I aliased the columns so you could see where the data was coming from. Since you said you can have 20 start/endDate columns, I added those in, but if needed, you can use dynamic SQL to make a more dynamic list.

这是一个有点复杂的问题，但是可以通过对unpivot数据的交叉应用来解决，然后使用一个PIVOT以使它恢复到您需要的格式。它在SQL 2017上进行了测试，但是我认为PIVOT和cte是在2005年添加的，ROW_NUMBER()是在2008年添加的，所以应该适用于您的SQL版本。在主查询中，我对列进行了别名，以便您可以看到数据是从哪里来的。既然您说您可以有20个start/endDate列，我添加了这些列，但是如果需要，您可以使用动态SQL来创建一个更动态的列表。

SQL Fiddle

SQL小提琴

MS SQL Server 2017 Schema Setup:

MS SQL Server 2017模式设置:

CREATE TABLE t1 ( Person_id bigint, Store_ID bigint, Startdate date, enddate date ) ;
INSERT INTO t1 (Person_id,Store_ID,Startdate,enddate)
VALUES 
    ( 10000351067,10000232561,'2016-09-09','2016-09-16')
  , ( 10000351067,10000232561,'2016-09-16','2016-10-03')
  , ( 10000351067,10000232561,'2016-10-03','2016-10-07')
  , ( 10000351067,10000232561,'2016-10-07','2017-01-17')
  , ( 10000351067,10000232561,'2017-01-17','2018-04-05')
  , ( 10000351067,10000232561,'2018-04-05',NULL)
  , ( 10000193858,10000225875,'2016-07-13','2016-08-03')
  , ( 10000193858,10000225875,'2016-08-03','2017-05-17')
  , ( 10000193858,10000225875,'2017-05-17','2017-06-05')
  , ( 10000193858,10000225875,'2017-05-31','2017-06-05')
  , ( 10000193858,10000225875,'2017-06-05','2017-06-13')
  , ( 10000193858,10000225875,'2017-06-13','2017-08-16')
  , ( 10000193858,10000225875,'2017-08-07','2017-08-16')
  , ( 10000193858,10000225875,'2017-08-16','2017-08-18')
  , ( 10000193858,10000225875,'2017-08-18','2017-08-31')
  , ( 10000193858,10000225875,'2017-08-31','2018-01-05')
  , ( 10000193858,10000225875,'2018-01-05',NULL)
;

Query 1:

查询1:

/* Start with a CTE to get the base data, plus the row number for the pivot colName */
; WITH cte_details AS (
  SELECT t1.Person_ID
    , t1.Store_ID
    , t1.StartDate
    , t1.EndDate
    , ROW_NUMBER() OVER ( PARTITION BY t1.Person_ID, t1.Store_ID ORDER BY t1.StartDate ) AS rn
  FROM t1
)
SELECT pvt.* 
FROM (
  /* Unpivot values, using a CROSS APPLY */
  SELECT cd.Person_ID, cd.Store_ID
    , crs.c + CAST(cd.rn AS varchar(5)) AS colName
    , crs.v AS colValue
  FROM cte_details cd
  CROSS APPLY (
    SELECT 'startDate', startDate UNION ALL
    SELECT 'endDate', endDate  
  ) crs (c, v)
) s1
/* Now PIVOT those back to get the results. */
PIVOT (
  max(s1.colValue) 
  FOR s1.colName IN (
        startDate1, endDate1, startDate2, endDate2, startDate3, endDate3
      , startDate4, endDate4, startDate5, endDate5, startDate6, endDate6
      , startDate7, endDate7, startDate8, endDate8, startDate9, endDate9
      , startDate10, endDate10, startDate11, endDate11, startDate12, endDate12
      , startDate13, endDate13, startDate14, endDate14, startDate15, endDate15
      , startDate16, endDate16, startDate17, endDate17, startDate18, endDate18
      , startDate19, endDate19, startDate20, endDate20      
   )
) pvt

Results:

结果:

|   Person_ID |    Store_ID | startDate1 |   endDate1 | startDate2 |   endDate2 | startDate3 |   endDate3 | startDate4 |   endDate4 | startDate5 |   endDate5 | startDate6 |   endDate6 | startDate7 |   endDate7 | startDate8 |   endDate8 | startDate9 |   endDate9 | startDate10 |  endDate10 | startDate11 | endDate11 | startDate12 | endDate12 | startDate13 | endDate13 | startDate14 | endDate14 | startDate15 | endDate15 | startDate16 | endDate16 | startDate17 | endDate17 | startDate18 | endDate18 | startDate19 | endDate19 | startDate20 | endDate20 |
|-------------|-------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|-------------|------------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|-------------|-----------|
| 10000193858 | 10000225875 | 2016-07-13 | 2016-08-03 | 2016-08-03 | 2017-05-17 | 2017-05-17 | 2017-06-05 | 2017-05-31 | 2017-06-05 | 2017-06-05 | 2017-06-13 | 2017-06-13 | 2017-08-16 | 2017-08-07 | 2017-08-16 | 2017-08-16 | 2017-08-18 | 2017-08-18 | 2017-08-31 |  2017-08-31 | 2018-01-05 |  2018-01-05 |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |
| 10000351067 | 10000232561 | 2016-09-09 | 2016-09-16 | 2016-09-16 | 2016-10-03 | 2016-10-03 | 2016-10-07 | 2016-10-07 | 2017-01-17 | 2017-01-17 | 2018-04-05 | 2018-04-05 |     (null) |     (null) |     (null) |     (null) |     (null) |     (null) |     (null) |      (null) |     (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |      (null) |    (null) |

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177410(v=sql.105)

https://docs.microsoft.com/en - us/previous versions/sql/sql -服务器- 2008 r2/ms177410(v = sql.105)

#2

if I understand correctly you want to UPDATE some fields WHERE certain conditions hold

如果我理解正确，您想要更新某些字段，其中包含某些条件

UPDATE 
SET Store_ID=10000232561, Startdate=2016-09-09, enddate=2016-09-16
WHERE Person_id = 10000351067

the documentation for microsoft's T-SQL is pretty good, see the update statement docs

微软的T-SQL文档非常好，请参阅update语句文档

#1