15,394,715 members
See more:
I have a problem ,i don't have any idea for this,i want to ask for someone help,

how can i split a row with
Data like this,

Emp_ID Emp_FullName leaves
002000089 Ricki 50

i want to change it into 5 row like this
Emp_ID Emp_FullName leaves
002000089 Ricki 12
002000089 Ricki 12
002000089 Ricki 12
002000089 Ricki 12
002000089 Ricki 2

What I have tried:

I mean i divide 50 with 12

how can i solve this?
Posted
Updated 19-May-22 22:24pm
Richard MacCutchan 20-May-22 3:05am

You probably need to do that in code. I do not know of any SQL function that would do do it. Take a look at SQL Tutorial[^].

## Solution 2

If you absolutely must do this in SQL then use a recursive CTE e.g.
SQL
```declare @demo table (a varchar(30), n varchar(30), num int)
insert into @demo (a, n, num) values ('002000089', 'Ricki', 50)

declare @factor int = 12;

;WITH q AS
(
SELECT  a, n, @factor as x, num from @demo
UNION ALL
SELECT  a, n, x, num - @factor
FROM    q
WHERE num > @factor
)
SELECT
a, n, case when num > @factor then x else num end as calc
FROM    q```
Maciej Los 21-May-22 12:05pm

5ed!

## Solution 3

One possible solution would be a recursive CTE:
SQL
```WITH cte As
(
SELECT
Emp_ID,
Emp_FullName,
CASE WHEN leaves < 12 THEN leaves ELSE 12 END As leaves,
leaves - 12 As Remaining
FROM
YourTable

UNION ALL

SELECT
Emp_ID,
Emp_FullName,
CASE WHEN Remaining < 12 THEN Remaining ELSE 12 END,
Remaining - 12
FROM
cte
)
SELECT
Emp_ID,
Emp_FullName,
leaves
FROM
cte
;```
However, by default you will get an error if a single row produces more than 100 output rows.

Another option would be to use a "numbers" or "tally" table:
SQL
```WITH cteTally As
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 As N
FROM
sys.all_columns
),
cteRanges As
(
SELECT
12 As Size,
N * 12 As StartValue,
(N + 1) * 12 As EndValue
FROM
cteTally
)
SELECT
E.Emp_ID,
E.Emp_FullName,
CASE
WHEN E.leaves > R.EndValue THEN R.Size
ELSE E.leaves - R.StartValue
END As leaves
FROM
YourTable As E
CROSS APPLY cteRanges As R
WHERE
T.leaves > R.StartValue
ORDER BY
E.Emp_ID,
R.StartValue
;```
Maciej Los 21-May-22 12:05pm

5ed!

## Solution 1

SQL is not a data processing library - it's a data storage and retrieval engine.

What you are trying to do is possible in SQL using temporary tables and a fair amount of code, but it's not really a practical thing to do - that kind of processing is much, much better done in your Presentation / Data Access layer language which will have much better facilities for that.
If only because when the requirements change (and boy, do they change ) the code will be a lot simpler to understand and modify - making your job easier and the app more reliable.