Joe Celko s SQL for Smarties - Advanced SQL Programming P52. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 482 CHAPTER 22 AUXILIARY TABLES SELECT seq MOD seq n-1 n n FROM Sequence As an example consider the following problem in which we want to display an output with what is called snaking in a report. Each ID has several descriptions and we want to see them in cycles of four n 4 when a department has more than four job descriptions we want to start a new row with an incremented position for each subset of four or fewer job descriptions. CREATE TABLE Company dept_nbr INTEGER NOT NULL job_nbr INTEGER NOT NULL sequence within department job_descr CHAR 6 NOT NULL PRIMARY KEY dept_nbr job_nbr INSERT INTO Company VALUES 1 1 1 2 1 3 desc1 desc2 desc3 INSERT INTO Company VALUES 2 1 desc4 2 2 desc5 2 3 desc6 2 4 desc7 2 5 desc8 2 6 desc9 INSERT INTO Company VALUES 3 1 desc10 3 2 desc11 3 3 desc12 I am going to use a view rather than a derived table to make the logic in the intermediate step easier to see. CREATE VIEW Foo2 dept_nbr row_grp d1 d2 d3 d4 AS SELECT dept_nbr MOD job_nbr 3 4 4 MAX CASE WHEN MOD job_nbr 4 1 The Sequence Table 483 THEN job_descr ELSE END AS d1 MAX CASE WHEN MOD job_nbr 4 2 THEN job_descr ELSE END AS d2 MAX CASE WHEN MOD job_nbr 4 3 THEN job_descr ELSE END AS d3 MAX CASE WHEN MOD job_nbr 4 0 THEN job_descr ELSE END AS d4 FROM Company AS F1 GROUP BY dept_nbr job_nbr SELECT dept_nbr row_grp MAX d1 AS d1 MAX d2 AS d2 MAX d3 AS d3 MAX d4 AS d4 FROM Foo2 GROUP BY dept_nbr row_grp ORDER BY dept_nbr row_grp Results dept_nbr row_grp dl d2 d3 d4 1 1 desc1 desc2 desc3 2 1 desc4 desc5 desc6 desc7 2 2 desc8 desc9 3 1 desc10 desc11 desc12 This is bad coding practice. Display is a function of the front end and should not be done in the database. Replacing an Iterative Loop While is not recommended as a technique and it will vary from SQL dialect to dialect replacing an iterative loop is a good exercise in learning to think in sets. You are given a quoted string made up of integers separated by commas and your goal is to break each of the integers out as a
