SQL server transform a column into several rows


Good Morning

I have a table called books that has 3 fields, Code, Name and Chapters Sample content:

32  - Jonas  - 4 

I need it to return the following result after the select

Codigo e Capitulo 
32       1
32       2
32       3
32       4

They told me to use cursor, but I'm not able to


Don't need a cursor. A possible solution could be as follows:

    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
    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 is a reference to the usual SQLFiddle .

Scroll to Top