sql server - Split row into several with SQL statement -

i have row in databasetable on following form:

id | amount |       | 5  | 5439   | 01.01.2014 | 05.01.2014 

i want split 1 row pr month using sql/t-sql:

 amount |         5439   | 01.01.2014   5439   | 02.01.2014   5439   | 03.01.2014   5439   | 04.01.2014  5439   | 05.01.2014 

i, sadly, cannot change database source, , want preferrably in sql trying result of query other table in powerpivot.

edit: upon requests on code, have tried following:

declare @counter int set @counter = 0 while  @counter < 6 begin     set @counter = @counter +1     select amount, dateadd(month, @counter, [from]) dato     [database].[dbo].[table] end 

this returns several databasesets.

you can use tally table generate dates.

sql fiddle

;with e1(n) as(     select 1 union select 1 union select 1 union select 1 union select 1 union     select 1 union select 1 union select 1 union select 1 union select 1 ), e2(n) as(select 1 e1 cross join e1 b), e4(n) as(select 1 e2 cross join e2 b), tally(n) as(     select top(select max(datediff(day, [from], [to])) + 1 yourtable)         row_number() over(order (select null))     e4 ) select      yt.id,     yt.amount,     [from] = dateadd(day, n-1, yt.[from]) yourtable yt cross join tally t     dateadd(day, n-1, yt.[from]) <= yt.[to] 

simplified explanation on tally table