Split a row into multiple rows based on a column value SQL

Question

I have the following "Order Table" :

    Item        Quantity
    pencil      2
    pen         1
    Notebook    4

I need the result like :

  Item        Quantity
  pencil      1
  pencil      1
  pen         1
  Notebook    1
  Notebook    1
  Notebook    1
  Notebook    1

You want a cross join: see this question/answer: stackoverflow.com/questions/4481396/… — CodeLikeBeaker
– CodeLikeBeaker, Commented Jan 6, 2016 at 14:13
sql server.. basically i want the item to be repeated the number of times as the initial quantity ordered, and quantity to be displayed as 1 in the final output for each item — HunTer
– HunTer, Commented Jan 6, 2016 at 14:22
@Surbhit why have a quantity at all in the output if it's always going to be 1? Just display the quantity as read from the DB. It will make your output easier to read and your code easier to maintain. — Esoteric Screen Name
– Esoteric Screen Name, Commented Jan 6, 2016 at 14:52

Tom H · Accepted Answer · 2016-01-06 14:21:35Z

1

You didn't specify which RDBMS you are using, so how you generate the numbers will depend on that (maybe a recursive CTE for SQL Server, using DUAL for Oracle, etc.). I've only written code to handle the data that you've shown, but you'll obviously need to account for numbers larger than four in the final solution.

SELECT
    MT.sr_no,
    MT.item_name,
    1 AS quantity
FROM
    My_Table MT
INNER JOIN
    (
        SELECT 1 AS nbr UNION ALL SELECT 2 AS nbr UNION ALL
        SELECT 3 AS nbr UNION ALL SELECT 4 AS nbr
    ) N ON N.nbr <= MT.quantity

answered Jan 6, 2016 at 14:21

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kiran Hegde · Accepted Answer · 2016-01-06 14:51:42Z

1

You can use the recursive query using common table expression to generate number duplicate rows according to the quantity field as below

WITH cte (sno,item,quantity,rnum)
AS
(
    SELECT sno,item,quantity, 1 as rnum
    FROM [Order]

    UNION ALL

    SELECT cte.sno,cte.item,cte.quantity, rnum+1
    FROM [Order] JOIN cte ON [Order].sno = cte.sno
    AND cte.rnum < [Order].quantity
)
SELECT item,1 AS Quantity 
FROM cte 
ORDER BY sno

edited Jan 6, 2016 at 14:51

answered Jan 6, 2016 at 14:40

Kiran Hegde

3,6811 gold badge18 silver badges14 bronze badges

Collectives™ on Stack Overflow

Split a row into multiple rows based on a column value SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related