Splitting single row into multiple based on column

Question

I have certain number of rows as below:

| Material No   | Quantity | Weight | Unit |
--------------------------------------------
| 111-22283/4   |    2     |   53   |  kg  |
| 123-ABC45/7   |    5     |   41   |   g  |
| 133-67879/80  |    7     |   31   |   g  |
| 144-54628     |    1     |   14   |  kg  |

Now I want to produce output like below:

| Material No   | Quantity | Weight | Unit |
--------------------------------------------
| 111-22283     |    2     |   53   |  kg  |
| 111-22284     |    2     |   53   |  kg  |
| 123-ABC45     |    5     |   41   |   g  |
| 123-ABC46     |    5     |   41   |   g  |
| 123-ABC47     |    5     |   41   |   g  |
| 133-67879     |    7     |   31   |   g  |
| 133-67880     |    7     |   31   |   g  |
| 144-54628     |    1     |   14   |  kg  |

Logic: Based on the material no I have to split the rows. If '/' at end of the material no then it needs to be spilt. Then we have to find the difference b/w last number in the material no and the number /. If it is 2 then I want 2 different rows with each number as material number(Means if the last digit is 83/4 then I want material number ends with 83 and 84). The tricky part is when we are having 89/90. It will contain 2 number after / (only for 10's,20's etc.,). All other column will remain as same for each material no.

To achieve this ,currently we have a very big procedure which contains around 50-80 lines of code( find the row with '/' and remove separately then find the index of / and so on). I would like to know if this can be done with simple query or a very short procedure.

Answer 1

UPDATE

When a value from MATERIALNO can represent a series of values (as the updated question) :

DDL

CREATE FUNCTION UFN_STRTOSERIES (@materialNo VARCHAR(80), @qte INT, @weight INT, @unit VARCHAR(50))  
RETURNS @result TABLE (
  MATERIALNO VARCHAR(80),
  QTE INT,
  Weight INT,
  UNIT VARCHAR(50)
)
AS  
BEGIN
  DECLARE @base VARCHAR(50) = LEFT(@materialNo,CHARINDEX('/',@materialNo)-1-LEN(RIGHT(@materialNo,LEN(@materialNo)-CHARINDEX('/',@materialNo))));
  DECLARE @start INT = CONVERT( INT , RIGHT( LEFT(@materialNo,CHARINDEX('/',@materialNo)-1) , LEN(RIGHT(@materialNo,LEN(@materialNo)-CHARINDEX('/',@materialNo))) ) );
  DECLARE @end INT = CONVERT( INT , RIGHT(@materialNo,LEN(@materialNo)-CHARINDEX('/',@materialNo)) );

  DECLARE @i INT = @start;
  WHILE @i <= @end
  BEGIN
    INSERT @result  
    SELECT CONCAT(@base,@i) AS MATERIALNO, @qte, @weight, @unit;
    SET @i = @i + 1;
  END;

  RETURN
END;

CREATE TABLE MATERIALS
(
  MATERIALNO VARCHAR(80),
  QTE INT,
  Weight INT,
  UNIT VARCHAR(50)
)

INSERT INTO MATERIALS VALUES
('111-22283/4',2,53,'kg'),
('123-33345/7',5,41,'g' ),
('123-ABC45/7',5,41,'g'),
('133-67879/80',7,31,'g'),
('144-54628',1,14,'kg')

DML

SELECT MATERIALNO,QTE,Weight,UNIT
FROM (
SELECT MATERIALNO,QTE,Weight,UNIT
FROM MATERIALS
WHERE CHARINDEX('/',MATERIALNO) < 1
UNION
SELECT series.MATERIALNO,series.QTE,series.Weight,series.UNIT
FROM MATERIALS m
CROSS APPLY UFN_STRTOSERIES(MATERIALNO,QTE,Weight,UNIT) series
WHERE CHARINDEX('/',m.MATERIALNO) > 1
) base
ORDER BY base.MATERIALNO

OLD ANSWER

When a value from MATERIALNO represents only two values :

UNION is the easiest answer (then you have to check performances) :

DDL

CREATE TABLE MATERIALS
(
  MATERIALNO VARCHAR(80),
  QTE INT,
  Weight INT,
  UNIT VARCHAR(50)
 )

 INSERT INTO MATERIALS VALUES
('111-22283/4',2,53,'kg'),
('123-33345/7',5,41,'g' ),
('133-67879/80',7,31,'g'),
('144-54628',1,14,'kg'  )

Query

SELECT MATERIALNO,QTE,Weight,UNIT
FROM (
SELECT MATERIALNO,QTE,Weight,UNIT
FROM MATERIALS
WHERE CHARINDEX('/',MATERIALNO) < 1
UNION
SELECT LEFT(MATERIALNO,CHARINDEX('/',MATERIALNO)-1),QTE,Weight,UNIT
FROM MATERIALS
WHERE CHARINDEX('/',MATERIALNO) > 1
UNION
SELECT 
CONCAT(LEFT(MATERIALNO,CHARINDEX('/',MATERIALNO)-1-LEN(RIGHT(MATERIALNO,LEN(MATERIALNO)-CHARINDEX('/',MATERIALNO)))),RIGHT(MATERIALNO,LEN(MATERIALNO)-CHARINDEX('/',MATERIALNO))) AS MATERIALNO
,QTE,Weight,UNIT
FROM MATERIALS
WHERE CHARINDEX('/',MATERIALNO) > 1
) BASE
ORDER BY BASE.MATERIALNO

Fiddle : http://sqlfiddle.com/#!18/8e768/2

Answer 2

the challenging part is probably splitting the material no to get the starting and ending number.

After that, it is just a simple recursive query to increment the number and concatenate back to form the material no.

;with rcte as
(
    select  MaterialNo,
            base, st, en, n = st,
            material    = convert(varchar(20), base + isnull(convert(varchar(10), st), ''))
    from    material m
            -- get the position of the `/`
            cross apply
            (
                select  split = charindex('/', MaterialNo)          
            ) s     
            -- extract the ending number and convert to integer
            cross apply
            (
                select  en  = case  when    split > 0
                                    then    convert(int, right(MaterialNo, len(MaterialNo) -  split))
                                    end     
            ) en
            -- extract the starting number and convert to integer
            cross apply
            (
                select  st  = case  when    split > 0
                        then    convert(int, substring(MaterialNo, split - len(en), len(en)))
                        end
            ) st
            -- extract the base material no for concatenate
            cross apply
            (
                select  base    = case  when    split   > 0
                                        then    left(MaterialNo, split - len(en) - 1)
                                        else    MaterialNo
                                        end
            ) b

    union all

    select  MaterialNo, base, st, en, 
            n = n + 1,
            material = convert(varchar(20), base + convert(varchar(10), n + 1))
    from    rcte 
    where   n < en
)
select  *
from    rcte
order by MaterialNo, material

all this is based on the assumption that the ending of material no is purely numeric.

Note : if you have a tally table, you can use that to replace the recursive cte