CODEWITHSUNDEEP
sqlsql-serverselect
Yorki
Yorki

Reputation: 135

T-SQL select statement to redistribute value in row into n rows

I have a table like (SQL Server 2016):

    ID    Month      Sales
    1     Jan 2019   40
    2     Feb 2019   80
    3     Mar 2019   400

...

would like to get sales redistributed by weeks (here we can assume each month is 4 weeks) like:

ID    Month      Sales
1     012019     10
1     022019     10
1     032019     10
1     042019     10
2     052019     20
2     062019     20
2     072019     20
2     082019     20
3     092019     100
3     102019     100
3     112019     100
3     122019     100
...

How can I achieve sth like that?

Upvotes: 1

Views: 110

Answers (1)

Mureinik
Mureinik

Reputation: 311308

You could join the query with a hard-coded query that generates four rows:

SELECT     id, month, sales / 4
FROM       mytable
CROSS JOIN (SELECT 1 AS col 
            UNION ALL
            SELECT 2
            UNION ALL
            SELECT 3
            UNION ALL
            SELECT 4) t

Upvotes: 2

Related Questions