More than one CTE in SQL Server 2005

As per Microsoft we can’t create nested CTE, means you cannot create CTE under CTE but you can create sub CTE kind of stuff. Let us see it practically how will it be possible?

--create one table and enter data for first CTE

CREATE TABLE Students(Name VARCHAR(15),Address VARCHAR(50),RollNo INT)

INSERT INTO Students VALUES ('Rashmi','Ahmedabad',1)

INSERT INTO Students VALUES ('Moni','Ahmedabad',2)

INSERT INTO Students VALUES ('Ritesh','Ahmedabad',3)

INSERT INTO Students VALUES ('Rushik','Ahmedabad',4)

--create second table and enter data for second CTE

CREATE TABLE StudentDetail(RollNo INT,SUBJECT VARCHAR(20),Marks INT)

INSERT INTO StudentDetail VALUES (1,'Science',75)

INSERT INTO StudentDetail VALUES (1,'Maths',95)

INSERT INTO StudentDetail VALUES (1,'English',65)

INSERT INTO StudentDetail VALUES (2,'Science',65)

INSERT INTO StudentDetail VALUES (2,'Maths',85)

INSERT INTO StudentDetail VALUES (2,'English',75)

INSERT INTO StudentDetail VALUES (3,'Science',95)

INSERT INTO StudentDetail VALUES (3,'Maths',85)

INSERT INTO StudentDetail VALUES (3,'English',45)

--first CTE

;WITH ST_CTE AS (

SELECT RollNo,Name,Address FROM Students

),

--second CTE

--don't put "WITH" statement in second CTE

STD_CTE AS (

SELECT RollNo,SUM(MARKS) AS Total,Convert(VARCHAR(5),SUM(MARKS)/Count(Rollno))+'%' As Percentage

FROM StudentDetail

GROUP BY RollNo

)

SELECT st.RollNo,Name,Address,Total,Percentage

FROM ST_CTE st

INNER JOIN STD_CTE std ON st.RollNo = std.RollNo

Reference: Ritesh Shah/Rashmika Vaghela

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com