split one rows to multiple rows in the table based on the size

Question

split one rows to multiple rows in the table based on the size

caojunhe24

Ten Centuries

Points: 1334
More actions
October 23, 2019 at 3:13 pm

#3691362

hi there,
am new to SQL and now am trying to do row split.
e.g currently i have two group of record sets in the table like this below.
id records keys date
1 196 100 1/2/2000
2 49 100 2/7/2000
so now all i want is split this 2 rows into multiple rows. if i have "chunk_row_size" variable =50 then i want to split it to 5 rows instead of 2.
output should like this below.
id records keys date
1 50 100 1/2/2000
2 50 100 1/2/2000
3 50 100 1/2/2000
4 46 100 1/2/2000
5 49 100 2/7/2000
any idea will be appreciate !
thanks!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

You'll need a numbers/tally table for this. Code below uses a built in one.


declare @chunk_row_size int = 50;
select row_number() over(order by t.id,ca.number) as id,
       case when (ca.number+1)*@chunk_row_size < t.records then @chunk_row_size else t.records - (ca.number*@chunk_row_size) end as records,t.keys,t.dt
from mytable t
cross apply (select n.number from master.dbo.spt_values n where n.type='p' and n.number <= t.records/@chunk_row_size) ca
order by id;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

caojunhe24 Ten Centuries Points: 1334 More actions · Answer 2

OMG... you are awesome !it's working perfectly but it's hard to understand based on my knowledge level.

thanks Mark!