SQL Server 将多行合并为一行多列
我有一个数据库，其中包含以下行:
I have a database in which I have the following rows:
ID | Date start | Date end
----------------------------------
a | 01-01-1950 | 30-01-1951
a | 01-01-1948 | 31-12-1949
a | 31-01-1951 | 01-06-2000
b | 01-01-1980 | 01-08-2010
c | 01-01-1990 | 31-12-2017
c | 31-01-1985 | 31-12-1989
我得到了什么
- 每人多行
- 每行一个开始和结束日期
- 按非时间顺序
选择我想返回以下内容的查询:
Select query which I want to return the following:
ID | Date start 1 | Date end 1 | Date start 2 | Date end 2 | Date start 3 | Date end 3
---------------------------------------------------------------------------------------------------
a | 01-01-1948 | 31-12-1949 | 01-01-1950 | 30-01-1951 | 31-01-1951 | 01-06-2000
b | 01-01-1980 | 01-08-2010
c | 31-01-1985 | 31-12-1989 | 01-01-1990 | 31-12-2017
我想要的:
- 每人一行
- 每行有多个开始和结束日期
- 按时间顺序
我能找到的大多数东西都希望它在同一列中，或者不希望它按时间顺序排序，所以不幸的是，这些情况不适用于我.
Most things I was able to find wanted it in the same column, or wouldn't want it sorted on chronological order, so unfortunately those situations didn't apply to me.
我现在真的知道如何解决这个问题了.
I really have now clue how to solve this.
推荐答案
如果你只有三个日期，那么pivot/conditional聚合应该没问题:
If you have only three dates, then pivot/conditional aggregation should be fine:
select id,
max(case when seqnum = 1 then dstart end) as start_1,
max(case when seqnum = 1 then dend end) as end_1,
max(case when seqnum = 2 then dstart end) as start_2,
max(case when seqnum = 2 then dend end) as end_2,
max(case when seqnum = 3 then dstart end) as start_3,
max(case when seqnum = 3 then dend end) as end_3
from (select t.*,
row_number() over (partition by id order by dstart) as seqnum
from t
) t
group by id;
注意:您必须指定输出中的列数.如果您不知道有多少，您可以:
Note: You have to specify the number of columns in the output. If you don't know how many there are, you can either:
- 生成动态 SQL 语句来提前进行计数.
- 手动计数并添加适当的列.
相关文章