CODEWITHSUNDEEP
sqlteradata
user1768029
user1768029

Reputation: 425

How to Split a row into multiple based on a column

I have to split a row into multiple rows based on a count column. if count is 6 , row should split into 2, first row's count will be 5 and next will be 1. If count is 17, there will be 4 split, 3 rows with 5 each and last count will be 2.

Table will have 2 columns :

    id    CNT
  ------------
    10    17

Above row should split into 4 like below:

    id    CNT
--------------------
    10    5
    10    5
    10    5
    10    2

I have tried using mod and division to find the total number of split but could not find final solution.

Please provide your input to achieve this.

Upvotes: 0

Views: 1280

Answers (2)

dnoeth
dnoeth

Reputation: 60462

DarkRob's recursive query can be simplified to

WITH RECURSIVE cte AS
 (
   SELECT id
      -- remainder or 5 if no remainder
     ,CASE WHEN Cnt MOD 5 = 0 THEN 5 ELSE Cnt MOD 5 END AS val
      -- how many rows left?
     ,(Cnt - val) / 5 AS x
   FROM tab
   WHERE Cnt > 0

   UNION ALL

   SELECT id, 5, x -  1
   FROM cte
   WHERE x > 0
 )
SELECT * 
FROM cte

But I would prefer (ab)using Teradata's Time Series Expansion feature:

SELECT tab.*,
   End(pd) - Begin(pd) AS val
FROM tab
WHERE Cnt > 0
-- create one rows for each group of 5 (days)
EXPAND ON PERIOD(DATE, DATE + Cnt) AS pd BY INTERVAL '5' DAY

Upvotes: 1

DarkRob
DarkRob

Reputation: 3833

You may try this. I am not sure about the syntax used in teradata, but this will surely give you idea about how you can sort out your problem.

First we need to find the quotient and remainder portion of each number with respect to the id. Then quotient is the number of times you want to repeat your loop, for which we are using recursive cte and remainder is the last row need to be added to get the sum of numbers equal to your value.

Sample query will be like this.

declare @tab table ( id int, num int )
insert into @tab ( id, num )
values ( 1, 17 )
, ( 2, 22 )

; with cte as (
select id, num , num %5 as remainder, num/5 as quo from @tab )
, ct (sl, id, num, val) as (
select 1 as sl, id, num, 5 as val from cte
union all
select c.sl+1, t.id, t.num, c.val from cte as t inner join ct as c on t.id=c.id where c.sl<t.quo
)
, cfinal (id, num, val) as (
select id, num, val from ct 
union all
select id, num, remainder from cte
)
select * from cfinal order by id, val desc

Result


id          num         val
----------- ----------- -----------
1           17          5
1           17          5
1           17          5
1           17          2
2           22          5
2           22          5
2           22          5
2           22          5
2           22          2

Upvotes: 2

Related Questions