問題描述

我在數(shù)據(jù)庫表中有一行采用以下形式:

I have a row in a databasetable that is on the following form:

ID | Amount | From       | To
5  | 5439   | 01.01.2014 | 05.01.2014

我想使用 SQL/T-SQL 將其拆分為一行 pr 月:

I want to split this up to one row pr month using SQL/T-SQL:

 Amount | From       
 5439   | 01.01.2014 
 5439   | 02.01.2014 
 5439   | 03.01.2014 
 5439   | 04.01.2014
 5439   | 05.01.2014

遺憾的是，我無法更改數(shù)據(jù)庫源，我想最好在 SQL 中執(zhí)行此操作，因?yàn)槲艺趪L試使用 Powerpivot 中的其他表來生成此查詢的結(jié)果.

I, sadly, cannot change the database source, and I want to preferrably do this in SQL as I am trying to result of this Query with an other table in Powerpivot.

根據(jù)對(duì)我的代碼的要求，我嘗試了以下操作:

Upon requests on my code, I have tried the following:

declare @counter int
set @counter = 0
WHILE  @counter < 6
begin
    set @counter = @counter +1
    select amount, DATEADD(month, @counter, [From]) as Dato
    FROM [database].[dbo].[table]
end

然而，這會(huì)返回多個(gè)數(shù)據(jù)庫集.

This however returns several databasesets.

推薦答案

您可以使用 tally table 生成所有日期.

You can use a tally table to generate all dates.

SQL 小提琴

;WITH E1(N) AS(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(SELECT MAX(DATEDIFF(DAY, [From], [To])) + 1 FROM yourTable)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
)
SELECT 
    yt.Id,
    yt.Amount,
    [From] = DATEADD(DAY, N-1, yt.[From])
FROM yourTable yt
CROSS JOIN Tally t
WHERE
    DATEADD(DAY, N-1, yt.[From]) <= yt.[To]

理貨表的簡單說明

這篇關(guān)于使用 SQL 語句將行拆分為多個(gè)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！