I have previously written many articles on CTE. One question I get often is how to use multiple CTE in one query or multiple CTE in SELECT statement. Let us see quickly two examples for the same. I had done my best to take simplest examples in this subject.
Option 1 :
/* Method 1 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT 2 AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Option 2:
/* Method 2 */
;WITH CTE1 AS (SELECT 1 AS Col1),
CTE2 AS (SELECT COL1+1 AS Col2 FROM CTE1)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO
Please note the difference between options. If there is any other option than above mentioned two options, please leave your comment here.
Reference : Pinal Dave (http://blog.sqlauthority.com)
Like this:
One blogger likes this post.
Thanx for sharing your knowledge.
I have question. I want to get information from a simple table but optional WHERE clause, but using CTE (for some purpose) How to accomplish my goal but with using string for query ???
@Aasim,
To accomplish CTE with optional parameter, you can use is as this way:
;WITH cte1 AS
( SELECT Col1
FROM Table
WHERE (@Param IS NULL OR Col1 = @Param)
),
cte2 AS (
SELECT Col2
FROM Table
WHERE (@Param IS NULL OR Col2 = @Param)
)
SELECT cte1.Col1,cte2.Col2
FROM cte1
CROSS JOIN cte2
GO
Here, if @param has value, then it will filter result in CTE.
Let me know if it helps you.
Thanks,
Tejas
@aasim
WITH CTE(String) AS (SELECT ‘a string’)
SELECT String FROM CTE;
” I had done my best to take simplest examples in this subject.”
These examples would be a lot easier to follow if you took an extra minute to use real column and table names. Practical real world scenarios are always the best examples.
Thanks
@Wes
“Practical real world scenarios are always the best examples.”
I disagree. For me, the best examples remove all unnecessary things and focus just on the issue. Only then should it be compared to reality.
But, to each their own.
Hello Sir,
I need your help for getting result from query.
I have three tables say ProductMaster, Genre and Product_Genre.
Records contained in ProductMaster table are like follows-
Product_ID ProductName Price
1 XYZ 20.10
2 ABC 11.35
3 PQR 05.33
Records contained in Genre table are like follows-
Genre_ID GenreName
101 Horror
102 Romantic
103 Suspense
Records contained in Product_Genre table are like follows-
Genre_ID ProductID
101 1
102 1
101 2
101 3
103 3
Now I want result like as follows-
Product_ID ProductName GenreName Price
1 XYZ Horror | Romantic 20.10
2 ABC Horror 11.35
3 PQR Horror | Suspense 05.33
Sir so how could I get this result please help me.
Thanks.
Regards,
Mandar Kavishwar
@Mandar kavishwar
The query itself is basic join:
WITH
ProductMaster(Product_ID, ProductName, Price)
AS
(
SELECT 1, ‘XYZ’, 20.10 UNION ALL
SELECT 2, ‘ABC’, 11.35 UNION ALL
SELECT 3, ‘PQR’, 05.33
),
Genre(Genre_ID, GenreName)
AS
(
SELECT 101, ‘Horror’ UNION ALL
SELECT 102, ‘Romantic’ UNION ALL
SELECT 103, ‘Suspense’
),
Product_Genre(Genre_ID, ProductID)
AS
(
SELECT 101, 1 UNION ALL
SELECT 102, 1 UNION ALL
SELECT 101, 2 UNION ALL
SELECT 101, 3 UNION ALL
SELECT 103, 3
)
SELECT
ProductMaster.Product_ID,
ProductMaster.ProductName,
Genre.GenreName,
ProductMaster.Price
FROM
ProductMaster,
Genre,
Product_Genre
WHERE
Product_Genre.Genre_ID = Genre.Genre_ID
AND Product_Genre.ProductID = ProductMaster.Product_ID;
But you also want to make the values comma-separated. For that, use XML. Pinal has a good example here:
http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/
hi Brian Tkatch,
I get following result when i execute query that u suggested me.
ProductID ProductName Genre Price
1 XYZ Horror 20.10
1 XYZ Romantic 20.10
2 ABC Horror 11.35
3 PQR Horror 5.33
3 PQR Suspense 5.33
But i want result like follows-
Product_ID ProductName GenreName Price
1 XYZ Horror | Romantic 20.10
2 ABC Horror 11.35
3 PQR Horror | Suspense 05.33
@Mandar
OK, the basic query is done. Now to make it comma separated, follow Pinal’s advise here: http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-column/
Hi,
I have small doubt. The results from the above query and for the below query were same.
Then why i have to use CTE. Can you explain? Actually, i am the beginner for CTE’s.
select ProductMaster.Product_ID,
ProductMaster.ProductName,
Genre.GenreName,
ProductMaster.Price
from ProductMaster PM
Inner Join Product_Genre P_G on PM.Product_Id = P_G.Productid
Inner Join Genre G On G.Genre_Id = P_G.Genre_Id
Waiting for your reply,
Thanks & Regards
Sri Sagar. P
Great articale I enjoyed . :)
Hi pinal,
Very useful article… i got this link when i was strucked with 2 CTE , it really helped me…
Thank you
regards,
Divya
What is the difference between CTE and table variables?
please give me answer..
You could say that CTE is like a ad-hoc view. When you call it it produces output from the data that is stored in the DB at the time of the call. Like you would select data from any normal table or view. One huge difference to normal table is that you can’t update or insert data to CTE.
Table variable on the other hand is a variable. You can store table data to it and select data from it multiple times and the data does not change even when the data in the DB changes. You can update or insert data to table variable also. And you can create indexes to table variable, something you can’t do with CTE.
Hello sir,
Great article; precisely the issue I will need your expert help on.
I have several columns that require Current Value (‘Reporting Week’) and YTD counts.
I felt like the best choice at solving this problem is to use CTE.
However, the issue I am running into is that after I get a sub total, how do I continue adding more columns?
For instance, I select the following columns:
StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms, Hazardous_MaterialsRespIncids and I get sub total called ‘Total Fire Incidents’ as shown below:
FIeldname Reporting Week YTD
————————————————————
StructuralFires 1 1
Non-StructuralFires 1 1
Non-Emergencies 0 4
Emergencies 1 3
Hazards 9 31
Total Fire Incidents 12 40
After this total, I want to continue selecting more columns.
Then eventually I get GrandTotal.
How do I do this?
This is the code I am working with so far. Please help me with your expertise.
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = ’9/26/2009′;
set @EndDate = ’10/2/2009′;
– Determine the year you are working with.
Set @ReportYear =
(
Select YEAR(StartDate)
From FireReportsData
Where (startDate = @StartDate and EndDate = @EndDate)
);
– Build the CTEs to hold your unpivoted data.
With WeeklyReport (IncidentType, [Reporting Week])
As
(
Select IncidentType, [Reporting Week]
From
(
Select ReportQID, StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms, Hazardous_MaterialsRespIncids
From FireReportsData
Where (startDate = @StartDate and EndDate = @EndDate)
) As FireData
UnPivot
(
[Reporting Week] For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms], [Hazardous_MaterialsRespIncids])
) As FireDataUnpivot
),
YtdReport (IncidentType, YTD)
As
(
Select IncidentType, [Reporting Week]
From
(
Select SUM(StructuralFires) As [StructuralFires],
SUM(Non_StructuralFires) As [Non_StructuralFires],
SUM(Non_Fire_Emergencies) As [Non_Fire_Emergencies],
SUM(Fire_Alarms) As [Fire_Alarms],
SUM(Hazardous_MaterialsRespIncids) As [Hazardous_MaterialsRespIncids]
From FireReportsData
Where (YEAR(StartDate) = @ReportYear)
) As FireData
UnPivot
(
[Reporting Week] For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms], [Hazardous_MaterialsRespIncids])
) As FireDataUnpivot
)
– Join the weekly and monthly results based off of incident type to produce
– your result set.
Select W.IncidentType, W.[Reporting Week], Ytd.YTD
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType
Union All
– Create a “totals” record.
Select ‘Total Fire Incidents’ As [IncidentType], SUM(W.[Reporting Week]) As [Reporting Week],
SUM(Ytd.YTD) As [YTD]
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType;
Go
i have a doubt!
Can we use Single CTE for multiple select statements?
Hi i have a query like this
With CTE1 AS
(
)
,CTE2 AS
(
)
If(@inputId = 0)
SELECT * from CTE1
else
SELECT * from CTE2
.Ca nanyone help me out how to solve this?
thanks you pinal. you are great for us. thanks a lot . why don’t you start a some coaching
Couldn’t have said it better myself Wonderful information Please keep blogging!!
Hi Krishnan,
After CTE there should be a SELECT and DML statements only. You can use SELECT/INSERT/UPDATE/DELETE only.
If you need to have condition then you have to use alternate way like Table variable.
Tejas
SQLYoga.com
Hi Pinal,
Can we run cte queries on our production DB.
Please suggest me.
Thanks
Rahul
@rahul.bhargava
Yes. CTEs are part of normal queries.
Hi i have a table having
IDNo code
1234 1
3456 1
2345 2
1456 2
1234 2
2345 1
1456 1
2806 2
here for duplicate id i need to select id no having code as 1
The output should be
idno code
1234 1
3456 1
2345 1
1456 1
2806 2
if i do that it is effecting other idno i heard it can be done with cte can anyone help me in solving above problem
@Raj
How’s this?
WITH
Data(IDNo, code)
AS
(
SELECT 1234, 1 UNION ALL
SELECT 3456, 1 UNION ALL
SELECT 2345, 2 UNION ALL
SELECT 1456, 2 UNION ALL
SELECT 1234, 2 UNION ALL
SELECT 2345, 1 UNION ALL
SELECT 1456, 1 UNION ALL
SELECT 2806, 2
)
SELECT
IDNo,
MIN(code)
FROM
Data
GROUP BY
IDNo;
Hi Raj,
You can use this solution, if you are using SQL 2005 and above.
I have used ROW_Number to identify if it is duplicate or not and get output as you want.
DECLARE @table TABLE(IDNO INT, Code INT)
INSERT INTO @table(IDNo, Code)
SELECT 1234, 1
UNION
SELECT 3456,1
UNION
SELECT 2345, 2
UNION
SELECT 1456, 2
UNION
SELECT 1234, 2
UNION
SELECT 2345, 1
UNION
SELECT 1456, 1
UNION
SELECT 2806, 2
;with cte as (
SELECT ROW_NUMBER() OVER(PARTITION BY IDNo ORDER BY Code) AS RowID,
IDNO,
Code
FROM @table
)
SELECT IDNO,
Code
FROM cte
WHERE RowID = 1
Thanks,
Tejas
SQLYoga.com
Hi Brian, Tejas Shah
Thanks for reply. i solved the issue in different way since i have 2million records..
with ctetbl1
as(
select affinityNumber from tbl
group by ID having COUNT(*)>1),
cteTemp
as(
select *
from tbl where AffinityNumber not in(select id from ctetbl1 where ID =’2′)
select * from cte temp
Thanks
Raj
CTEs(Common Table Expressions) are one of the beautiful and the most powerful feature of SQL Server and are boon to SQL Server developers. These not only simplifies most of the complex operations in T-SQL but also do a lot more than one can imagine.
h ttp://www.sqllion.com/2010/08/common-table-expressions-cte/
I have two tables tblParent and tblmembers
tblParent
———
parentid(PK), parentname, address, age
tblMembers
———-
membername, age, sex, parentid(FK)
——————————-
My report should show like this:
——————————–
100 Edwin QRY 50
1 Sushi 45 F
2 Craig 26 M
3 Mira 23 F
101 John TVM 30
1 Sara 26 F
2 Sam 3 M
____________________________________________
Thanks in advance…. :)
You need to post the sample data with the logic
Gracias eso es lo que estaba buscando.
hi pinal…
today i faced new challenge, i was using CTE to remove duplicate from one table but now i have to join table then i have to remove duplicate.
but im geting this error:
”View or function ‘dup’ is not updatable because the modification affects multiple base tables”
plz replay
Can you post the code you used?
table1(t1_id,t1_name)
table2(t1_id,t2_name)
table3(t1_id,t3_name)
WITH dup(t1_name,t2_name,t3_name,duplicount)
AS(SELECT t1.t1_name,t2.t2_name,t3.t3_name
ROW_NUM() OVER(PARTITION_BY t1.t1_name,t2.t2_name,t3.t3_name ORDER BY t1.t1_name)AS duplicount from table1 t1
inner join table2 t2
on t1.t1_id=t2.t1_id
inner join table3 t3
on t1.t1_id=t3.t1_id)
delete from dup
above is the example plz reply as soon as posible
You cannot delete a CTE which is formed using more than one table
Which table do you want to delete data?
i want to remove duplicate and info is linked in different table.
for eg. if customer name, add store in one table then same customer phone no. in phone table.
so if customer name,add and phone no. are twise delete the record.
thanx madhivanan for the support
waiting for result
Search for Delete duplicates in this site
Hi Pinal,
Is there any level for recursive CTE? How many levels we can go?
Please reply.
Thanks
Anand
[...] SQL Server – Multiple CTE in One SELECT Statement Query [...]
thank you, this post helped me to solve one problem. I am happy
Good luck
Dok
Now I want to solve one problem again.
I want to make columns in a table like below:
Id Column1 Column2 Column3
1 5 5 => Same as Column1 5 => Same as Column2
2 2 12 => column1 current + column2.prev + column3.previous = 2+5+5 17 => column2.current + column3.prev = 12+5
3 3 32 => 3+12+17
easier way to see:
Id Column1 Column2 Column3
1 5 5 => Same as Column1 5 => Same as Column2
2 2 12 => 2+5+5 17 => 12+5
3 3 32 => 3+12+17 49 => 32+17
I am looking forward any answer and it will be appreciated. Thank you in advance
Dok
Id | Column1 | Column2 | Column3
1 | 5 | 5 => Same as Column1 | 5 => Same as Column2
2 | 2 | 12 => 2+5+5 | 17 => 12+5
3 | 3 | 32 => 3+12+17 | 49 => 32+17
Still a useful article 2 years later. I have to say, I like the elegance of method 1, but after many rounds of performance testing, I found method 2 to run faster.
Thanks for the article. It solved my problem
Hi i need clarification for this.my req. is this how should i do
With CTE1 AS
(
)
,CTE2 AS
(
)
If(@inputId = 0)
SELECT * from CTE1
else
SELECT * from CTE2
[...] and Answers ISBN: 1466405643 Page#109-112 Common Table Expression (CTE) and Few Observation Multiple CTE in One SELECT Statement Query Delete Duplicate Rows Simple Example of Recursive CTE SQL SERVER – Simple Example of Recursive [...]
[...] on CTE: Simple Example of Recursive CTE Multiple CTE in One SELECT Statement Query Common Table Expression (CTE) and Few Observation Delete Duplicate Rows Simple Example of Recursive [...]