sql - 多行值到同一行和不同列
问题描述
我有一个包含 4 个字段的表Person_id
,Store_id
和。对于 的特定值,可以有许多具有不同开始日期和结束日期的记录。在这里,我需要将多行值更新为同一行和不同的列。startdate
enddate
person_id
示例 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
示例 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
结果集:
例如1:
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...
例如2:
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....
解决方案
这是一个有点令人费解的问题,但可以通过取消CROSS APPLY
透视数据来解决,然后通过 aPIVOT
将其恢复为您需要的格式。它在 SQL 2017 上进行了测试,但我相信PIVOT
CTE 是在 2005 年和ROW_NUMBER()
2008 年添加的,因此应该可以在您的 SQL 版本中使用。在主查询中,我为列设置了别名,以便您可以看到数据的来源。既然你说你可以有 20start/endDate
列,我添加了这些,但如果需要,你可以使用动态 SQL 来制作更动态的列表。
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)
;
查询 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
结果:
| 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)
推荐阅读
- python-3.x - 如何在 Tensorflow 的 NumpyReader 中插入多元字典?
- reactjs - bundle.js 未找到,在浏览器上得到 404
- c# - Visual Studio 15.7.0 xamarin 表单构建错误
- inno-setup - 强制在 Inno Setup 中始终选择一项任务
- php - SOAP 的请求 xml 不包含 PHP 7 上的对象数组
- html5-canvas - 如何使用上下文弧法创建右侧有切口的画布圆形图像?
- r - 根据多列中以下行中的值更新行
- c++11 - 如何用整数类型本身为整数类型专门化一个类模板?
- jquery - 猫头鹰旋转木马不显示图像
- python-3.x - 使用漂亮的汤从 web 表中提取数据