none
Replicate a records from a view RRS feed

  • Question

  • Good Morning

    I'm new to the forum and are not sure if the question is in the correct forum.

    I have a view following:

    View X

    Colunm Product | Colunm amount

     XPTO              |         25

      AB                 |          2

    But instead of show XPTO - 25 and AB - 2 i need show 25 records of XPTO - 1 and 2 records de AB - 1.

    If it were a procedure I could create a repeating but accurate structure of the result with return in a View.

    Is there any way to create a view that returns me the data this way?

    Friday, September 14, 2018 11:21 AM

Answers

  • yes

    using a number table 

    like

    CREATE VIEW vw_YourViewName
    AS
    SELECT v.[Column Product] + ' - 1'
    FROM ViewX v
    CROSS JOIN master..spt_Values p
    WHERE type = 'p'
    AND number BETWEEN 1 AND v.[Column Amount]
    GO


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Jonathas_A_B Friday, September 14, 2018 12:02 PM
    Friday, September 14, 2018 11:31 AM

All replies

  • yes

    using a number table 

    like

    CREATE VIEW vw_YourViewName
    AS
    SELECT v.[Column Product] + ' - 1'
    FROM ViewX v
    CROSS JOIN master..spt_Values p
    WHERE type = 'p'
    AND number BETWEEN 1 AND v.[Column Amount]
    GO


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by Jonathas_A_B Friday, September 14, 2018 12:02 PM
    Friday, September 14, 2018 11:31 AM
  • Perfect, thank you.
    Friday, September 14, 2018 12:05 PM
  • Good morning Visakh16, in deeper test I realized that there is apparently a limitation of 2047 the number of the cross join. Is there a way to increase this number?

    I tried to use CTE with a view to get around the situation, but I came across the problem of OPTION (MAXRECURSION 0) because I use an ETL to connect my application to the database and MAXRECURSION can not stay inside the view. Follow the CTE


    CREATE VIEW [dbo].[vSequence] AS
    WITH gen AS (
        SELECT 0 AS num
        UNION ALL
        SELECT num+1 
    FROM gen cross join vMaxSaldo
    WHERE num+1<= saldo
    )
    SELECT  *
    FROM gen --  option (maxrecursion 10000)

    Friday, September 14, 2018 2:03 PM
  • Good morning Visakh16, in deeper test I realized that there is apparently a limitation of 2047 the number of the cross join. Is there a way to increase this number?

    I tried to use CTE with a view to get around the situation, but I came across the problem of OPTION (MAXRECURSION 0) because I use an ETL to connect my application to the database and MAXRECURSION can not stay inside the view. Follow the CTE


    CREATE VIEW [dbo].[vSequence] AS
    WITH gen AS (
        SELECT 0 AS num
        UNION ALL
        SELECT num+1 
    FROM gen cross join vMaxSaldo
    WHERE num+1<= saldo
    )
    SELECT  *
    FROM gen --  option (maxrecursion 10000)

    Thats not limitation on cross join but number of p type rows on master..spt_values

    ANyways if you want to use your own number table you can do like below

    create view vw_YourViewName
    AS
    With Number(N)
    AS
    (
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 UNION ALL
    SELECT 1 
    ),NumMatrix 
    AS
    (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Seq
    FROM Number n1
    CROSS JOIN NUmber n2
    CROSS JOIN NUmber n3
    CROSS JOIN NUmber n4
    CROSS JOIN NUmber n5
    CROSS JOIN NUmber n6
    )
    
    SELECT p.[Column Product] + '-1' AS [Column Product]
    FROM NumMatrix n
    CROSS JOIN ViewX p
    WHERE Seq BETWEEN 1 AND p.[Column Amount]
    GO
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, September 14, 2018 2:21 PM