Click here to Skip to main content
15,394,715 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a problem ,i don't have any idea for this,i want to ask for someone help,

it is about sql query,

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
Comments
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[^].

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
   
Comments
Maciej Los 21-May-22 12:05pm
   
5ed!
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
;
   
Comments
Maciej Los 21-May-22 12:05pm
   
5ed!
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.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900