none
SQL return multiple rows from one record

    Question

  • Hi Experts,

    I would like to my sql statement to return multiple rows from one record, I have an orderitem table and based on quantity it should display that many records. Example see below.

    OrderItem Table Result.

    OIID OrderID ProdCode Qty UnitPrice
    10012 191 PROD01 1 24.00
    10013 191 PROD04 3 10.00

    SQL Query Result looking as below

     

    OIID OrderID ProdCode UnitPrice
    10012 191 PROD01 24.00
    10013 191 PROD04 10.00
    10013 191 PROD04 10.00
    10013 191 PROD04 10.00

    Can someone please advise how can I acheive above result with simple sql query so i can create a view on the database?

    Kind Regards

    Bhavesh


    Bhavesh
    Wednesday, October 27, 2010 6:19 PM

Answers

  • u can do like this..

    declare @Maxqty int = (select MAX(Qty) from ORderItem)
    
    ;with cte as
    (
    	select 1 as Num
    	union all
    	select Num + 1 from cte where Num < @Maxqty
    )
    select O.* from OrderItem O
    inner join cte c on c.Num <= O.Qty 
    

    Otherwise, if u have any numbers table in your database, say "tblNumbers" with column "Number" u can directly write as

    select O.* from OrderItem O
    inner join tblNumbers c on c.Number <= O.Qty 
    

    Wednesday, October 27, 2010 6:26 PM
  • Do you know what First Normal Form (1NF) is and why what you are doing should be handled in the front end? 

    You can do a stinking dirty kludge with a Series table and and a CROSS JOIN, if you don't care about good SQL programming. 


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Wednesday, October 27, 2010 7:17 PM

All replies

  • u can do like this..

    declare @Maxqty int = (select MAX(Qty) from ORderItem)
    
    ;with cte as
    (
    	select 1 as Num
    	union all
    	select Num + 1 from cte where Num < @Maxqty
    )
    select O.* from OrderItem O
    inner join cte c on c.Num <= O.Qty 
    

    Otherwise, if u have any numbers table in your database, say "tblNumbers" with column "Number" u can directly write as

    select O.* from OrderItem O
    inner join tblNumbers c on c.Number <= O.Qty 
    

    Wednesday, October 27, 2010 6:26 PM
  • Do you know what First Normal Form (1NF) is and why what you are doing should be handled in the front end? 

    You can do a stinking dirty kludge with a Series table and and a CROSS JOIN, if you don't care about good SQL programming. 


    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Wednesday, October 27, 2010 7:17 PM