SQL server transform a column into several rows

Question:

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

Answer:

Don't need a cursor. A possible solution could 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 is a reference to the usual SQLFiddle .

Scroll to Top