CODEWITHSUNDEEP
sql-server-2008recursionwith-statement
edgarmtze
edgarmtze

Reputation: 25058

complex With query on SQL Server 2008

Could you please explain what do these lines of code do?

Table 1: INT,VARCHAR,FLOAT

ID Name  value
---------------------------
1   a1  32116580
2   a2  50785384
3   a3  54327508
4   a4  61030844

;with coords(i,row,col,total,N) as (
select 1,1,1,N.N*(N.N+1)/2, N.N
from (select count(*) N from table1) N
union all
select i+1,
       case when col+1>N then row+1 else row end,
       case when col+1>N then row+1 else col+1 end,
       total, N
from coords
where i<total
)

I know with provides a way to write auxiliary statements for use in a larger query, so it is like if I declare some variables I would use, but after that I am a little confused...And Why the use of case for getting row and col; Also why in the case there are two: case when col+1>N then row+1 else , How does SQL know when to do one case or the other?...

i  row col total N
--------------------
1   1   1   10   4
2   1   2   10   4
3   1   3   10   4
4   1   4   10   4
5   2   2   10   4
6   2   3   10   4
7   2   4   10   4
8   3   3   10   4
9   3   4   10   4
10  4   4   10   4

Upvotes: 1

Views: 1209

Answers (2)

Will A
Will A

Reputation: 25008

The columns of table1 can be ignored since only the count is being used - thus you can rewrite your CTE as:

;with coords(i,row,col,total,N) as (
    select 1, 1, 1, 10, 4
    union all
    select i+1,
           case when col+1>N then row+1 else row end,
           case when col+1>N then row+1 else col+1 end,
           total, N
    from coords
    where i<total
    )
SELECT * FROM coords

...with a SELECT * FROM coords there to visualise the results.

The select 1, 1, 1, 10, 4 'seeds' the recursion - this is the row from which later levels of the recursion will derive. The rest of the rows are built up from the second query acting upon (initially) the seed row and then the resultant row from the second query fed back in to itself etc.

Upvotes: 2

Dustin Laine
Dustin Laine

Reputation: 38533

It is creating a common table expression.

It is basically create a temporary table structure from the select query within. The select statement is doing the following:

1) Selecting a bunch of default values 1,1,1 and a calculation N.N*(N.N+1)/2 and finally a value from the N table N.N
2) UNIONing in another select statement.
3) The second select is doing some conditional output with the case statements.

Upvotes: 0

Related Questions