问题描述
我在数据库表中有一行采用以下形式:
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]
理货表的简单说明