I came across some interesting ways to use common table expressions (CTE) in SQL Server and pass them on in the hope someone may find them useful. The first example shows how to use a CTE to update a table, and the second illustrates a technique for comparing a row with a previous row.<\/p>\n
Update a CTE<\/strong><\/p>\n Suppose you inherit a table that keeps track of tools. For this example, assume tools reside in toolboxes, only in boxes, and only one box at a time. Furthermore, the toolboxes cannot be nested like matryoshka<\/em>. The rows are in no particular order:<\/p>\n Now suppose your requirements change and you must assign a number to each tool, by toolbox. You could use a cursor to iterate over the rows and maintain a counter, but I’ve always found them clumsy.<\/p>\n Add a new integer column, RECORD_NUM<\/strong> and run a CTE to generate a count for each row by toolbox, and then update the CTE with the count:<\/p>\n Nice and neat. RECORD_NUM<\/strong> must be selected by the CTE in order to make it available to the update statement. The SELECT 1<\/strong> in the ORDER BY<\/strong> clause does not provide order of any kind, which is fine because the rows are in no particular order. When run, is updates the table to the following:<\/p>\n\n <\/p>\n Compare with Previous Row<\/strong><\/p>\n Computing the difference of a value between one date and the next is a common task, but you have to jump through a few hoops to get an answer with SQL.<\/p>\n Here we have FOO.FOOBAR<\/strong> and we want to know the change of VALUE<\/strong> from month to month.<\/p>\n CTEs and ranking functions provide a clean implementation.<\/p>\nToolbox | Tool \n----------------- \nRed | Pliers \nRed | Hammer \nRed | Robertson screwdriver \nBlue | Wire cutters \nBlue | Utility knife \nBlue | Phillips screwdriver<\/pre>\n
;WITH CTE_RN AS (\n SELECT ROW_NUMBER() OVER (\n PARTITION BY TOOLBOX ORDER BY (SELECT 1)) AS RN,\n RECORD_NUM FROM TOOLBOX ) \nUPDATE CTE_RN SET CTE_RN.RECORD_NUM=CTE_RN.RN<\/pre>\n
Toolbox | Tool | RECORD_NUM\n--------------------------------------------\nRed | Pliers | 1\nRed | Hammer | 2\nRed | Robertson screwdriver | 3 \nBlue | Wire cutters | 1\nBlue | Utility knife | 2\nBlue | Phillips screwdriver | 3\n<\/pre>\n
DATE | VALUE\n------------------\n2018-01-01 | 10\n2018-02-01 | 20\n2018-03-01 | 30\n2018-04-01 | 40\n2018-05-01 | 50\n<\/pre>\n
;WITH CTE_DATES AS (\n SELECT [DATE], VALUE,\n DENSE_RANK OVER (ORDER BY [DATE] DESC) AS RK\n FROM FOO.FOOBAR )\nSELECT\n CUR.DATE AS CURRENT,\n PREV.DATE AS PREVIOUS,\n CUR.VALUE - PREV.VALUE AS DIFF FROM CTE_DATES CUR\n INNER JOIN CTE_DATES PREV ON PREV.RK=(CUR.RK + 1)\n ORDER BY CUR.DATE DESC\n<\/pre>\n