CODEWITHSUNDEEP
sqlsql-servert-sql
Kaja
Kaja

Reputation: 3057

Repeat one row in SQL Server n times - according on a column

I have this data in one of my tables:

YEAR    MONTH     Cost
-------------------------
2018      1       25000
2018      2       32000
2018      3        9865

I would like to repeat rows for example if Month=1 then I would like to have 31 times this row, if Month=2 then 28 times for this row (actually according to number of days of each month)

How can I do that?

Thank you so much

Upvotes: 0

Views: 2934

Answers (6)

Livius
Livius

Reputation: 956

recursive CTE is you friend here assuming that your table name is T2

with A as (
    select T.[year], T.[month], datefromparts(T.[year], T.[month], 1) AS D, T.[cost]
    from T2 T
    union all
    select T.[year], T.[month], dateadd(day, 1, A.D) AS D, T.[cost]
    from T2 T INNER JOIN A ON T.[year]=A.[year] AND T.[month]=A.[month] AND T.[cost]=A.[cost]
    where MONTH(dateadd(day, 1, A.D)) = MONTH(A.D)
)
SELECT * FROM A
ORDER BY A.[year], A.[month], A.D

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

And yet one more:

DECLARE @tbl TABLE(y INT, m INT, Cost INT)
INSERT INTO @tbl VALUES
 (2018,1,25000)
,(2018,2,32000)
,(2018,3,9865);

--the tally-CTE will generate 31 running numbers in just no time
--this CTE is used with TOP with a calculated count to retrieve the fitting count of numbers

WITH Tally AS(SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
                                  ,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
                                  ,(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31)) t(nr))
SELECT *
FROM @tbl t
CROSS APPLY(SELECT CONVERT(DATETIME,CONCAT(t.y,REPLACE(STR(t.m,2),' ','0'),'01'),126)) D(FirstOfMonth)
CROSS APPLY(SELECT TOP (DAY(DATEADD(MONTH,1,D.FirstOfMonth)-1)) nr FROM Tally ORDER BY nr) AS multiplier;

the first cross apply will compute the first of your month ("20180101"), while the computation in TOP will add one month and go one day back. This is the last day of the month.

You did not specify your SQL-Serer version. This will be much easier with EOMONTH (v2012+) and DATEFROMPARTS (v2012+)

UPDATE with SQL-Server 2012

try this with v2012+

WITH Tally AS(SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
                                  ,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
                                  ,(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31)) t(nr))
SELECT *
FROM @tbl t
CROSS APPLY(SELECT TOP (DAY(EOMONTH(DATEFROMPARTS(t.y,t.m,1)))) nr FROM Tally ORDER BY nr) AS multiplier

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

This uses an inline table valued function with CROSS APPLY:

create function YearsMonths(@year int, @month int)
returns table
as
return
    Select @year [Year], @month [Month]
    from    master.dbo.spt_values t2 
    where t2.type = 'P' AND t2.number < CASE WHEN @month IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN @month IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (YEAR(@year) % 4    = 0 AND
                                YEAR(@year) % 100 != 0) OR
                               (YEAR(@year) % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END
GO
create table yearmonthcost ([Year] int, [Month] int, [Cost] int)
insert into yearmonthcost values
  (2018, 1, 25000)
, (2018, 2, 32000)
, (2018, 3, 9865)

select * 
from yearmonthcost ymc
cross apply dbo.YearsMonths(ymc.[Year], ymc.[Month])

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

You should have a calendar table to which you can LEFT JOIN your table or you can build one using numbers table/tally table.

Here's an excellent article to read about this concept.

One way to do this demonstrated below-

select year, month,date=t.d cost from 
your table cross apply(
select
d=dateadd(d,r ,cast(cast(month as varchar(2))+ '-01-'+cast(year as varchar(4)) as date))
from
(
select top 31
r=row_number() over( order by (select null))-1
from
sys.objects s1 cross join sys.objects s2)t
where month(dateadd(d,r ,cast(cast(month as varchar(2))+ '-01-'+cast(year as varchar(4)) as date)))=month
)t

Upvotes: 0

EzLo
EzLo

Reputation: 14189

Use a calendar table, it will solve this and many future dates problems for you.

This solution generates one with a recursive CTE.

DECLARE @StartDate DATE = '2018-01-01'
DECLARE @EndDate DATE = '2020-01-01'

;WITH GeneratedCalendar AS
(
    SELECT
        GeneratedDate = @StartDate,
        Month = MONTH(@StartDate),
        Year = YEAR(@StartDate)
    UNION ALL
    SELECT
        GeneratedDate = DATEADD(DAY, 1, G.GeneratedDate),
        Month = MONTH(DATEADD(DAY, 1, G.GeneratedDate)),
        Year = YEAR(DATEADD(DAY, 1, G.GeneratedDate))
    FROM
        GeneratedCalendar AS G
    WHERE
        G.GeneratedDate < @EndDate
)
SELECT
    T.YEAR,
    T.MONTH,
    T.Cost,
    G.GeneratedDate
FROM
    YourTable AS T
    INNER JOIN GeneratedCalendar AS G ON
        T.YEAR = G.Year AND
        T.MONTH = G.Month
ORDER BY
    T.YEAR,
    T.MONTH
OPTION
    (MAXRECURSION 30000)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use a recursive CTE:

with cte as (
      select year, month, datefromparts(year, month, 1) as dte, cost
      from t
      union all
      select year, month, dateadd(day, 1, dte), cost
      from t
      where day(dateadd(day, 1, dte)) <> 1
     )
select *
from cte;

This includes the date for each row.

Upvotes: 0

Related Questions