問題描述
我有一個數(shù)據(jù)庫，其中包含以下行:
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
我得到了什么
- 每人多行
- 每行一個開始和結(jié)束日期
- 按非時間順序
選擇我想返回以下內(nèi)容的查詢:
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
我想要的:
- 每人一行
- 每行有多個開始和結(jié)束日期
- 按時間順序
我能找到的大多數(shù)東西都希望它在同一列中，或者不希望它按時間順序排序，所以不幸的是，這些情況不適用于我.
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.
我現(xiàn)在真的知道如何解決這個問題了.
I really have now clue how to solve this.
推薦答案
如果你只有三個日期，那么pivot/conditional聚合應(yīng)該沒問題:
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;
注意:您必須指定輸出中的列數(shù).如果您不知道有多少，您可以:
Note: You have to specify the number of columns in the output. If you don't know how many there are, you can either:
- 生成動態(tài) SQL 語句來提前進行計數(shù).
- 手動計數(shù)并添加適當?shù)牧?
這篇關(guān)于SQL Server 將多行合并為一行多列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！