Replicate a records from a view

Category: sql server samples

Question

Jonathas_A_B on Fri, 14 Sep 2018 11:21:52


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?

Replies

Visakh16 on Fri, 14 Sep 2018 11:31:56


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

Jonathas_A_B on Fri, 14 Sep 2018 12:05:13


Perfect, thank you.

Jonathas_A_B on Fri, 14 Sep 2018 14:03:55


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)

Visakh16 on Fri, 14 Sep 2018 14:21:04


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