使用 SQL 语句将行拆分为多个

2021-09-10 00:00:00 sql tsql sql-server

我在数据库表中有一行采用以下形式:

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

遗憾的是，我无法更改数据库源，我想最好在 SQL 中执行此操作，因为我正在尝试使用 Powerpivot 中的其他表来生成此查询的结果.

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.

根据对我的代码的要求，我尝试了以下操作:

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

然而，这会返回多个数据库集.

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]

理货表的简单说明

相关文章