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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: