Sql server turn a column into multiple rows

Asked

Viewed 967 times

3

Good morning

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

32  - Jonas  - 4

i need after select me return the following result 

Codigo e Capitulo 
32       1
32       2
32       3
32       4

I was told to use cursor, but I’m not getting

sql sql-server cursor

  • show only one more example of data. It seems to me that nothing needs to be done to the Name field?

  • Really do not need to do anything with the name field, what will define the number of lines is the number of chapters as in example 4 could give as an example the book 19 - Psalms - 150 Chapters there would repeat the code 19 by 150 times going from chapter 1 to 150

  • Add to the question the code you have already done using the cursor.

  • The Code I made is completely wrong, that’s why I didn’t add it,

  • You’ll do a function with this?

1 answer

2


No cursor needed. A possible solution may be the following:

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.

  • Very good your solution. Just one detail. It is interesting to use variable table instead of temporary in this case.

  • Thanks, solved my problem

Browser other questions tagged sql sql-server cursor

You are not signed in. Login or sign up in order to post.