Split row into several with SQL statement

问题:

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

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

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.

Edit: 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.

回答1:

You can use a tally table to generate all dates.

SQL Fiddle

;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]

Simplified explanation on Tally Table

回答2:

You need a tally table with "running numbers". This may be a function (I posted one shortly here: https://stackoverflow.com/a/32096945/5089204) or a physical table (I posted an example here: https://stackoverflow.com/a/32474751/5089204) or a CTE to do this "on the fly" (the table example does it this way).

If you go with the posted function it could be like this:

declare @startDate DATETIME={d'2015-09-01'};
declare @EndDate DATETIME={d'2015-09-10'};
select DATEADD(DAY, Nmbr,@startDate) 
from dbo.GetRunningNumbers(DATEDIFF(DAY,@startDate,@endDate)+1,0);

回答3:

select * INTO #TEMP1 from 
(values
(5  , 5439   , '01.01.2014', '05.01.2014'))t(id,amount,fromd,tod)

WITH CTE
AS
(
    SELECT CAST(FROMD AS DATE) AS FROMD,amount,1 AS RN,ID FROM #TEMP1
    UNION ALL
    SELECT DATEADD(M,1,C.FROMD),C.amount,C.RN+1,C.ID 
    FROM CTE C 
            INNER JOIN #TEMP1 T ON T.id = C.ID AND DATEADD(M,1,c.FROMD)<=T.tod

)

SELECT * FROM CTE

回答4:

create table t (fd date, td date)
insert into t values ('2015-01-01','2015-01-05')

WITH DATES (fd, td, Level)
AS
(
    SELECT fd, td, 0 AS Level
    FROM t
    UNION ALL
-- Recursive member definition
    SELECT DATEADD(day,level+1,e.fd),e.td,Level + 1
    FROM t AS e
    INNER JOIN Dates AS d ON DATEADD(day,-d.level,d.fd) = e.fd AND d.fd < d.td

)
-- Statement that executes the CTE
SELECT fd,td,level
from DATES

回答5:

variant using recursive cte

--variable table for data sample
DECLARE @tbl AS TABLE
    (
      ID INT ,
      Amount FLOAT ,
      [From] DATE ,
      [To] DATE
    )
INSERT  INTO @tbl
        ( ID, Amount, [From], [To] )
VALUES  ( 5, 5439, '2014-01-01', '2014-01-05' )

--final query using recursive cte
;
WITH    cte
          AS ( SELECT   T.ID ,
                        T.Amount ,
                        T.[From] ,
                        T.[To] ,
                        CONVERT(DATE, NULL) AS Dt ,
                        n = 0
               FROM     @tbl AS T
               UNION ALL
               SELECT   cte.ID ,
                        cte.Amount ,
                        cte.[From] ,
                        cte.[To] ,
                        DATEADD(DAY, n, cte.[From]) ,
                        cte.n + 1
               FROM     cte
               WHERE    n <= DATEDIFF(day, cte.[From], cte.[To])
             )
    SELECT  cte.ID ,
            cte.Amount ,
            dt AS [From]
    FROM    cte
    WHERE   cte.Dt IS NOT NULL

SQL Fiddle