Good morning
I have a table called books that has 3 fields, Code, Name and Chapters Example content:
32 - Jonas - 4
I need that after the select return the following result to me
Codigo e Capitulo
32 1
32 2
32 3
32 4
I've been told to use a cursor, but I'm not able to
You do not need a cursor. One possible solution might be as follows:
CREATE TABLE #Livros
(
Codigo INT,
Nome NVARCHAR(25),
Capitulos INT,
)
INSERT INTO #Livros(Codigo, Nome, Capitulos)VALUES
(32, 'Jonas', 4)
;WITH MaxCapitulos AS
(
SELECT MAX(Capitulos) AS MaxCap
FROM #Livros
),
Capitulos AS
(
SELECT 1 Cap
UNION ALL
SELECT Cap + 1
FROM Capitulos
WHERE Cap < (SELECT MaxCap FROM MaxCapitulos)
)
SELECT Lv.Codigo, Cp.Cap
FROM #Livros Lv
INNER JOIN Capitulos Cp
ON Cp.Cap <= Lv.Capitulos
OPTION (maxrecursion 0)
Here's a reference to the usual SQLFiddle .