問題描述

我有一個數(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)！