SQL Server – Multiple CTE in One SELECT Statement Query

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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQLAuthority News – Humorous SQL Cake – Funny SQL Cake
Next Post
SQLAuthority News – Interview with SQL Server MVP Glenn Berry

Related Posts

73 Comments. Leave new

  • Gracias eso es lo que estaba buscando.

    Reply
  • 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

    Reply
    • Can you post the code you used?

      Reply
      • Brijesh Bellur
        February 9, 2011 5:10 pm

        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

    Reply
  • thank you, this post helped me to solve one problem. I am happy
    Good luck

    Dok

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
    • Hi, you can try it so:

      with cte as
      (
      SELECT * from table
      where 1=1 and inputId = 0

      union all

      SELECT * from table
      where 1=1 and @inputId 0
      )
      select * from cte

      Reply
      • Sorry, the correct text:

        SELECT * from table
        where 1=1 and @inputId = 0

        union all

        SELECT * from table
        where 1=1 and @inputId != 0

  • can you help me with this kind of query

    category table
    having categoryID,categoryName,parentCategory,categoryStatus

    i want to have just like this output

    categoryID | CategoryName | ParentCategory | CategoryStatus
    1 Shoes null Active
    2 Shoes>Air 1 Active
    3 Shoes>Air>Jordan 2 Active

    Hope you might help thanks ;)

    Reply
  • Hi Pinal,

    You are a legend, everytime I google for a T SQL problem and your website comes up – I know my problem is solved!.

    This example was especially well presented.

    Thanks!
    VB

    Reply
  • Hi Friends,

    Can you suggest me different options to fire multiple select statement simultaneously from the same table except using sql script in the oracle and mysql

    Reply
  • Your post helped me solve multiple CTE issue.
    Thanks for the article

    Reply
  • Im trying to insert the results into a table but I keep receiving error: Invalid object name ‘perms’. If within my WITH statement I only use a single select statement, it works. Any suggestions?

    IF OBJECT_ID (N’dbo.tempdbperms’, N’U’) IS NOT NULL
    DROP TABLE dbo.tempdbperms;
    GO
    CREATE TABLE dbo.tempdbperms
    (
    [ServerName] [nvarchar](100) NULL,
    [dbname] [nvarchar](100) NULL,
    [principal_name] [nvarchar](50) NULL,
    [principal_id] [nvarchar](50) NULL,
    [principal_type_desc] [nvarchar](100) NULL,
    [class_desc] [nvarchar](50) NULL,
    [object_name] [nvarchar](100) NULL,
    [permission_name] [nvarchar](50) NULL,
    [permission_state_desc] [nvarchar](50) NULL
    );
    GO
    DECLARE @DBNAME nvarchar (1000) = (SELECT DB_NAME());
    DECLARE @ServerName nvarchar (1000) = (select @@servername);
    WITH perms ( [ServerName],
    [dbname],
    [principal_name],
    [principal_id],
    [principal_type_desc],
    [class_desc],
    [object_name],
    [permission_name],
    [permission_state_desc])

    AS
    (
    select @ServerName as ServerName,
    @DBNAME as dbname,
    USER_NAME(p.grantee_principal_id) AS principal_name,
    dp.principal_id,
    dp.type_desc AS principal_type_desc,
    p.class_desc,
    OBJECT_NAME(p.major_id) AS object_name,
    p.permission_name,
    p.state_desc AS permission_state_desc
    from sys.database_permissions p
    inner JOIN sys.database_principals dp
    on p.grantee_principal_id = dp.principal_id
    )

    –users

    SELECT @ServerName as ServerName,
    @DBNAME as dbname,
    p.principal_name,
    p.principal_type_desc,
    p.class_desc, p.[object_name],
    p.permission_name,
    p.permission_state_desc,
    cast(NULL as sysname) as role_name
    FROM perms p
    WHERE principal_type_desc ‘DATABASE_ROLE’

    UNION

    –role members

    SELECT @ServerName as ServerName,
    @DBNAME as dbname,
    rm.member_principal_name,
    rm.principal_type_desc,
    p.class_desc,
    p.object_name,
    p.permission_name,
    p.permission_state_desc,
    rm.role_name
    FROM perms p
    right outer JOIN
    (
    select @ServerName as ServerName,
    @DBNAME as dbname,
    role_principal_id,
    dp.type_desc as principal_type_desc,
    member_principal_id,
    user_name(member_principal_id) as member_principal_name,
    user_name(role_principal_id) as role_name–,*
    from sys.database_role_members rm
    INNER JOIN sys.database_principals dp
    ON rm.member_principal_id = dp.principal_id

    ) rm

    ON rm.role_principal_id = p.principal_id
    order by 1;
    GO
    INSERT INTO dbo.tempdbperms
    SELECT *
    FROM perms;
    GO

    Reply
  • Thanks, Pinal Dave!

    Reply
  • Need help on a CTE…

    We have a recursive chaining that was done and I need to get the first part of the chain for all rows in the table.

    Another words…

    Table Widget
    PK_id old_id new_id
    1 1 2
    2 2 3
    3 3 4
    4 15 16
    5 16 17
    6 17 18
    7 18 19
    8 100 105
    9 105 110
    10 110 91
    11 777 111
    12 111 401
    13 401 845
    There are 4 chains there and I need to bring back the first of the chain so I would get:
    1
    15
    100
    777

    I have a query that can bring back the entire chain but can’t figure out how to get the beginning of the chain for each recursive chain. The query below is not exactly correct as you may notice. It brings back one extra row which is not correct.

    With CTE (new_id, old_id, rn, level)
    as
    (select old_id, new_id, ROW_NUMBER() OVER ( ORDER BY PK_id) AS RN, 0 as level
    from xref_system LatestSystem where old_id =’777′
    union all
    select c.new_id, c.old_id, pc.RN, Level + 1
    from widget as c
    inner join cte as pc on c.old_id = pc.new_id
    )
    select new_id, old_id, RN, level
    from CTE
    ORDER BY rn, LEVEL

    This brings back:
    new_id old_id RN level
    777 111 1 0
    111 777 1 1
    401 111 1 2
    845 401 1 3

    So, there are 2 questions….
    1) The above query is not correct.
    2) Would like a query to go through an entire table and bring back the following result:
    1
    15
    100
    77

    Thanks in advance!
    Abbi

    Reply
  • Hi Mandar Kavishwar,
    Here is your solution without cursor, using CTE,

    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
    ),

    cte3 as(
    SELECT
    row_number() over(partition by ProductMaster.Product_ID order by ProductMaster.Product_ID) num,
    Product_ID,
    ProductName,
    Genre.GenreName,
    Price
    FROM
    ProductMaster,
    Genre,
    Product_Genre
    WHERE
    Product_Genre.Genre_ID = Genre.Genre_ID
    AND Product_Genre.ProductID = ProductMaster.Product_ID — )test
    ),

    cte4 as
    (
    select * from cte3 where num=1
    ),

    cte5 as
    (
    select num,product_id,null as ProductName,GenreName,null as price from cte3 where num1
    )

    select cte4.Product_ID,
    cte4.ProductName,
    cast(cte4.GenreName as varchar(100)) + (case when cte5.GenreName is null then ” else ‘ | ‘ end)
    + (case when cte5.GenreName is null then ” else cte5.GenreName end) as GenreName,
    cte4.Price
    from cte4 left outer join cte5 on cte4.Product_ID = cte5.Product_ID

    Reply
  • GREAT STUFF! The comma between the two cte definitions was the missing link for me…
    Thanks for the effort

    Reply
  • hi sir,
    please help me my problem, single field name in multiple comma separated value to use like qury and get value how write the query?
    sample column..

    Software testing,maual testing,automation testing
    PHP,Multimedia
    PHP,ASP.NET,NET,Html
    testing,Javascript,Html
    Asp.net,Sql Server,Software testing
    Asp.net,PHP

    Reply
  • hello sir,

    i have problem with my query as i want to use ;with stat. with if else condition

    my query is.

    declare @code nvarchar(10)
    ;with dte as
    (
    select b.rmname as [Resource Plan],b.rmabbr as [Code],a.rate as [Cost Rs./Hrs],a.totalhours as [Total Hrs.], a.rate as [Billing Rate] from b_PMestimationDetails a
    join dbo.b_rmmaster b on a.resource=b.rmcode
    –where tranno=’DPM-400004′
    ),
    dte1 as
    (
    if @code=(select code from dte)
    begin
    select [Resource Plan],Code,[Cost Rs./Hrs],(select SUM([Total Hrs.]) from dte where Code=@code) as Hrs,[Billing Rate] from dte
    end
    else
    begin
    select [Resource Plan],Code,[Cost Rs./Hrs],[Total Hrs.],[Billing Rate] from dte
    end
    )

    select * from dte1

    Reply
  • Sir,
    I have an issue regarding the duplicity of data in the application report as well as in table of database.
    Descriptions:
    –> The problem arises in the database side itself.
    –> The duplicity of row is in the table, the data are fetched from the different hardware components.

    ISSUE: Here we are running 3 PACs(Precession Air Conditioner) which is monitored by the Metasys Software and the events has been occurred in the database(here the duplicity occurs)

    With Regards
    S Sudharsanan

    Reply

Leave a Reply