First, the anchor part is pulling distinct [CategoryID] from table dbo.Products and two empty string columns, product_list and product_name, and 0 as lenght. Well, I do not know why they choose [length] instead cycle or pass. If we work with CategoryID = 1, then you will have a result like this from the anchor part:

CategoryId

product_list product_name

length

1

0

Now the recursive part bring all ProductName with same CategoryID and ProductName > product_name, in this case all products with same CategoryID and ProductName > ''. Then for each row in the result from the first pass of recursive part, it will concatenate the product_name to the product_list list column from the anchor part. The result of the first pass on the recursive part will be something like:

CategoryId

product_list

product_name

length

1

Chai

Chai

1

1

Chang

Chang

1

1

Guaraná Fantástica

Guaraná Fantástica

1

1

Sasquatch Ale

Sasquatch Ale

1

1

Steeleye Stout

Steeleye Stout

1

1

Côte de Blaye

Côte de Blaye

1

1

Chartreuse verte

Chartreuse verte

1

1

Ipoh Coffee

Ipoh Coffee

1

1

Laughing Lumberjack Lager

Laughing Lumberjack Lager

1

1

Outback Lager

Outback Lager

1

1

Rhönbräu Klosterbier

Rhönbräu Klosterbier

1

1

Lakkalikööri

Lakkalikööri

1

From that result, pull again all products with same CategoryID and ProductName > product_list, but notice that it will be done for each row, so for the product_name = 'Chai', the list will be 'Chang','Guaraná Fantástica', ..., 'Lakkalikööri'. The result will be a string of the concatenation of the previous product_list plus the new product_name. The result of the second pass for product_name 'Chai' will be:

CategoryId

product_list

product_name

length

1

Chai, Chang

Chang

2

1

Chai, Guaraná Fantástica

Guaraná Fantástica

2

1

Chai, Sasquatch Ale

Sasquatch Ale

2

1

Chai, Steeleye Stout

Steeleye Stout

2

1

Chai, Côte de Blaye

Côte de Blaye

2

1

Chai, Chartreuse verte

Chartreuse verte

2

1

Chai, Ipoh Coffee

Ipoh Coffee

2

1

Chai, Laughing Lumberjack Lager

Laughing Lumberjack Lager

2

1

Chai, Outback Lager

Outback Lager

2

1

Chai, Rhönbräu Klosterbier

Rhönbräu Klosterbier

2

1

Chai, Lakkalikööri

Lakkalikööri

2

The same for product_name 'Chang'.

CategoryId

product_list

product_name

length

1

Chang, Guaraná Fantástica

Guaraná Fantástica

2

1

Chang, Sasquatch Ale

Sasquatch Ale

2

1

Chang, Steeleye Stout

Steeleye Stout

2

1

Chang, Côte de Blaye

Côte de Blaye

2

1

Chang, Chartreuse verte

Chartreuse verte

2

1

Chang, Ipoh Coffee

Ipoh Coffee

2

1

Chang, Laughing Lumberjack Lager

Laughing Lumberjack Lager

2

1

Chang, Outback Lager

Outback Lager

2

1

Chang, Rhönbräu Klosterbier

Rhönbräu Klosterbier

2

1

Chang, Lakkalikööri

Lakkalikööri

2

Notice that the number of rows for 'Chai' is greater than the one for 'Chang', this is because of the filter expression. In other words, because the list has to be sort by ProductName, each pass concatenate previous product_list with each one of the products matching ProductName > product_name, were product_name is the name of the product in the last list. At the end, you will have a a lot of rows but the only one you need is the one that include the concatenation of all products for that category, in other words the list with the greates number of products in its list and for each CategoryID. That row si the one with greates [length] or number of pass, and this is being calculated using the ranking function RANK, but you can use any of the ranking functions in this case.

RANK

()OVER(PARTITIONBY CategoryId ORDERBYlengthDESC)

because the order is DESC, then the greatest number of pass will be 1 and the final statement pull just those rows, where [rank] = 1.

As you can see it is complicated and there are a lot of calculation (concatenation) that at the end are wasted.

Hope this help and you do not get dizzy by my English.

For you second question, I guess that the problem could be that in order to use WITH as a CTE, you need a semicolon before it in order to tell SS that it is a CTE and not the reserved word used, for example, to specify options.

I have converted this sql as per my db structure and it is giving me the required results but I'm still not able to understand completely how exactly this is working.

Secondly, I need to combine these results with my other SQL. That SQL is having more then 8 Tables with lot of complex joins but yesterday when I tried to join these results with my sql it was giving me error that semicolon is missing.

First, the anchor part is pulling distinct [CategoryID] from table dbo.Products and two empty string columns, product_list and product_name, and 0 as lenght. Well, I do not know why they choose [length] instead cycle or pass. If we work with CategoryID = 1, then you will have a result like this from the anchor part:

CategoryId

product_list product_name

length

1

0

Now the recursive part bring all ProductName with same CategoryID and ProductName > product_name, in this case all products with same CategoryID and ProductName > ''. Then for each row in the result from the first pass of recursive part, it will concatenate the product_name to the product_list list column from the anchor part. The result of the first pass on the recursive part will be something like:

CategoryId

product_list

product_name

length

1

Chai

Chai

1

1

Chang

Chang

1

1

Guaraná Fantástica

Guaraná Fantástica

1

1

Sasquatch Ale

Sasquatch Ale

1

1

Steeleye Stout

Steeleye Stout

1

1

Côte de Blaye

Côte de Blaye

1

1

Chartreuse verte

Chartreuse verte

1

1

Ipoh Coffee

Ipoh Coffee

1

1

Laughing Lumberjack Lager

Laughing Lumberjack Lager

1

1

Outback Lager

Outback Lager

1

1

Rhönbräu Klosterbier

Rhönbräu Klosterbier

1

1

Lakkalikööri

Lakkalikööri

1

From that result, pull again all products with same CategoryID and ProductName > product_list, but notice that it will be done for each row, so for the product_name = 'Chai', the list will be 'Chang','Guaraná Fantástica', ..., 'Lakkalikööri'. The result will be a string of the concatenation of the previous product_list plus the new product_name. The result of the second pass for product_name 'Chai' will be:

CategoryId

product_list

product_name

length

1

Chai, Chang

Chang

2

1

Chai, Guaraná Fantástica

Guaraná Fantástica

2

1

Chai, Sasquatch Ale

Sasquatch Ale

2

1

Chai, Steeleye Stout

Steeleye Stout

2

1

Chai, Côte de Blaye

Côte de Blaye

2

1

Chai, Chartreuse verte

Chartreuse verte

2

1

Chai, Ipoh Coffee

Ipoh Coffee

2

1

Chai, Laughing Lumberjack Lager

Laughing Lumberjack Lager

2

1

Chai, Outback Lager

Outback Lager

2

1

Chai, Rhönbräu Klosterbier

Rhönbräu Klosterbier

2

1

Chai, Lakkalikööri

Lakkalikööri

2

The same for product_name 'Chang'.

CategoryId

product_list

product_name

length

1

Chang, Guaraná Fantástica

Guaraná Fantástica

2

1

Chang, Sasquatch Ale

Sasquatch Ale

2

1

Chang, Steeleye Stout

Steeleye Stout

2

1

Chang, Côte de Blaye

Côte de Blaye

2

1

Chang, Chartreuse verte

Chartreuse verte

2

1

Chang, Ipoh Coffee

Ipoh Coffee

2

1

Chang, Laughing Lumberjack Lager

Laughing Lumberjack Lager

2

1

Chang, Outback Lager

Outback Lager

2

1

Chang, Rhönbräu Klosterbier

Rhönbräu Klosterbier

2

1

Chang, Lakkalikööri

Lakkalikööri

2

Notice that the number of rows for 'Chai' is greater than the one for 'Chang', this is because of the filter expression. In other words, because the list has to be sort by ProductName, each pass concatenate previous product_list with each one of the products matching ProductName > product_name, were product_name is the name of the product in the last list. At the end, you will have a a lot of rows but the only one you need is the one that include the concatenation of all products for that category, in other words the list with the greates number of products in its list and for each CategoryID. That row si the one with greates [length] or number of pass, and this is being calculated using the ranking function RANK, but you can use any of the ranking functions in this case.

RANK

()OVER(PARTITIONBY CategoryId ORDERBYlengthDESC)

because the order is DESC, then the greatest number of pass will be 1 and the final statement pull just those rows, where [rank] = 1.

As you can see it is complicated and there are a lot of calculation (concatenation) that at the end are wasted.

Hope this help and you do not get dizzy by my English.

For you second question, I guess that the problem could be that in order to use WITH as a CTE, you need a semicolon before it in order to tell SS that it is a CTE and not the reserved word used, for example, to specify options.