You must Sign In to post a response.
  • Category: SQL Server

    how to display same row multiple times in sql server

    I have a table 'Product' in SQL database like as below

    Table: Product
    ProdID ProductName Price Quantity
    1 xyz1 5000 3
    2 xyz2 2000 2
    3 xyz3 3000 5
    4 xyz4 4000 7

    I need sql query which return repeated row 3 times (depend on quantity) filter by ProdID like as below

    1 xyz1 5000 3
    1 xyz1 5000 3
    1 xyz1 5000 3

    Could any one please assist me to write the query?
  • #762787
    Hello Abul Bashar Sardar,

    I have done it using while loop as per your need :

    DECLARE @site_value INT;
    SET @site_value = 0;

    WHILE @site_value <3
    BEGIN
    SELECT p.ProdID, p.ProductName, p.Price, p.Quantity FROM Product p INNER JOIN Product p1 ON p.ProdID = p1.ProdID WHERE p.Quantity = 3
    SET @site_value = @site_value + 1;
    END;

    GO


    Check it and let me know whether if satisfies your requirement or not.


    Hope it will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #762789
    Hi,

    For generating records based on your requirement ie query which returns repeated row 3 times (depend on quantity) filter by ProdID

    You can try to use the non-equi join and generate duplicate records based on your quantity column

    CREATE TABLE Products (
    ProdId int identity(1,1),
    ProdName varchar(100),
    Quantity int
    )
    INSERT INTO Products select 'Will repeat 3 Times', 3
    INSERT INTO Products select 'Will repeat 2 Times', 2
    INSERT INTO Products select 'Will repeat 4 Times', 4
    INSERT INTO Products select 'Will repeat Only once', 1

    SELECT P1.ProdId, P1.ProdName, P1.Quantity from Products P1
    JOIN Products P2
    ON P1.Quantity >= P2.Quantity
    order by P1.ProdId

    I hope this might help you

    Thanks & Regards,
    Mahesh Durgam

  • #762791
    I got it, thanks Nirav Lalan for your sincere cooperation...

  • #762792
    Hi Nirav.

    Instead of hard coding p.Quantity = 3, try some other logic that should work depending up on the quantity column. If quantity is 3 or 2 or 4 it should work.

    @Abul Bashar Sardar.
    Can you post how you achieved that task, so that similar requirement people can benefit from it.

    Sridhar Thota.
    Editor: DNS Forum.

  • #762798
    CREATE PROCEDURE ProductList @ProdID int
    AS
    DECLARE @i int, @j int
    SET @i=(SELECT ProductInfo.Quantity FROM ProductInfo WHERE ProductInfo.ProdID=@ProdID);
    SET @j=0
    WHILE(@j<@i)
    BEGIN
    SELECT Product, Quantity, Price
    FROM ProductInfo
    WHERE ProductInfo.ProdID = @ProdID
    SET @j=@j+1
    END
    GO

  • #763298
    Hello,

    I have another way to execute this query.
    You can also try this :-

    Select * From (Select ProdID ProductName Price Quantity From Product
    UNION ALL
    Select ProdID ProductName Price Quantity From Product
    UNION ALL
    Select ProdID ProductName Price Quantity From Product) as tbl order by ProdID

    This query will also helps you to solve this issue.

    Thanks


Sign In to post your comments