SQL - List all pages in between record while maintaining ID key

Question

I'm trying to come up with a useful way to list all pages in between the first of last page of a document into new rows while maintaining the ID number as a key, or cross reference. I have a few ways of getting pages in between, but I'm not exactly sure how to maintain the key in a programmatic way.

Example Input:

First Page  Last Page  ID
ABC_001     ABC_004    1
ABC_005     ABC_005    2
ABC_006     ABC_010    3

End Result:

All Pages  ID
ABC_001    1
ABC_002    1
ABC_003    1
ABC_004    1
ABC_005    2
ABC_006    3
ABC_007    3
ABC_008    3
ABC_009    3
ABC_010    3

Any help is much appreciated. I'm using SQL mgmt studio.

How do you know what the keys are of the "in-between" pages? — Gordon Linoff, Mar 14 '19 at 00:14
The key or ID will be the same as either the first, or last page where the record is from just as you see in the end result. So in this example there is only a total of 3 ID/keys. — JohnnySemicolon, Mar 14 '19 at 00:33

score 2 · Accepted Answer · answered Mar 14 '19 at 01:17

One approach would be to set up a numbers table, that contains a list of numbers that you may possibly find in the column content:

CREATE TABLE numbers( idx INTEGER);
INSERT INTO numbers VALUES(1);
INSERT INTO numbers VALUES(2);
...
INSERT INTO numbers VALUES(10);

Now, assuming that all page values have 7 characters, with the last 3 being digits, we can JOIN the original table with the numbers table to generate the missing records:

SELECT 
    CONCAT(
        SUBSTRING(t.First_Page, 1, 4), 
        REPLICATE('0', 3 - LEN(n.idx)),
        n.idx
    ) AS [ALl Pages],
    t.id
FROM
    mytable t
    INNER JOIN numbers n 
        ON  n.idx >= CAST(SUBSTRING(t.First_Page, 5, 3) AS int)
        AND n.idx <= CAST(SUBSTRING(t.Last_Page, 5, 3) AS int)

This demo on DB Fiddle with your sample data returns:

ALl Pages | id
:-------- | -:
ABC_001   |  1
ABC_002   |  1
ABC_003   |  1
ABC_004   |  1
ABC_005   |  2
ABC_006   |  3
ABC_007   |  3
ABC_008   |  3
ABC_009   |  3
ABC_010   |  3

Vesper Annstas · Answer 2 · 2019-03-14T06:25:37.127

To find all pages from First Page to Last Page per Book ID, CAST your page numbers from STRING to INTEGER, then add +1 to each page number until you reach the Last Page.

First, turn your original table into a table variable with the Integer data types using a TRY_CAST.

DECLARE @Book TABLE (
    [ID]        INT
   ,[FirstPage] INT
   ,[LastPage]  INT
)

INSERT INTO @Book
SELECT  [ID]
       ,TRY_CAST(RIGHT([FirstPage], 3) AS int) AS [FirstPage]
       ,TRY_CAST(RIGHT([LastPage], 3) AS int)  AS [LastPage]
FROM [YourOriginalTable]

Set the maximum page that your pages will increment to using a variable. This will cap out your results to the correct number of pages. Otherwise your table would have many more rows than you need.

DECLARE @LastPage   INT 
SELECT @LastPage = MAX([LastPage]) FROM @Book

Turning a three-column table (ID, First Page, Last Page) into a two-column table (ID, Page) will require an UNPIVOT.

We're tucking that UNPIVOT into a CTE (Common Table Expression: basically a smart version of a temporary table (like a #TempTable or @TableVariable, but which you can only use once, and is a little more efficient in certain circumstances).

In addition to the UNPIVOT of your [First Name] and [Last Name] columns into a tall table, we're going to append every other combination of page number per ID using a UNION ALL.

;WITH BookCTE AS (
    SELECT [ID]
          ,[Page]
    FROM (SELECT [ID]
                ,[FirstPage]
                ,[LastPage]
          FROM @Book) AS bp
    UNPIVOT
    (
        [Page] FOR [Pages] IN ([FirstPage], [LastPage])
    ) AS up
    UNION ALL
    SELECT [ID], [Page] + 1 FROM BookCTE WHERE [Page] + 1 < @LastPage
)

Now that your data is held in a table format using a CTE with all combinations of [ID] and [Page] up to the maximum page in your @Book table, it's time to join your CTE with the @Book table.

SELECT DISTINCT
        cte.ID
       ,cte.Page
FROM BookCTE AS cte
    INNER JOIN @Book AS bk
        ON bk.ID = cte.ID
WHERE cte.Page <= bk.[LastPage]
ORDER BY
       cte.ID
      ,cte.Page
OPTION (MAXRECURSION 10000)

SQL - List all pages in between record while maintaining ID key

2 Answers2