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

About these ads

66 thoughts on “SQL Server – Multiple CTE in One SELECT Statement Query

  1. 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 ???

  2. @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

  3. ” 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.

  4. 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

        • 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

          • The only reason I see using a with statement here is to avoid creating the tables. I recommend a cursor for your request.

            I’ve changed your joins to outer in case you have a title without a Genre assigned or there is an orphaned entry in Product_Genre.

            Also, I’d change the ‘union all’ to a simple union to reduce possible duplicity. Now ABC is twice as horrific as you might think. Just remove the single ‘all’ and it will just be horror.

            declare @priorID int, @priorName varchar(3), @priorPrice varchar(6), @priorGenreCurrent varchar(max);
            declare @pID int, @pName varchar(3), @pPrice varchar(6), @pGenreCurrent varchar(20), @pGenreTxt varchar(max);
            declare @pCounter int;

            select @pCounter = 1;
            select @priorID = 0;
            select @priorName = null;
            select @priorPrice = null;
            select @priorGenreCurrent = null;
            select @pGenreTxt = null;

            declare myCursor cursor for
            –If you have tables comment out the with block
            –Begin with block
            WITH
            ProductMaster(Product_ID, ProductName, Price)
            AS
            (
            SELECT 1, ‘XYZ’, 20.10 UNION
            SELECT 2, ‘ABC’, 11.35 UNION
            SELECT 3, ‘PQR’, 05.33
            ),
            Genre(Genre_ID, GenreName)
            AS
            (
            SELECT 101, ‘Horror’ UNION
            SELECT 102, ‘Romantic’ UNION
            SELECT 103, ‘Suspense’
            ),
            Product_Genre(Genre_ID, ProductID)
            AS
            (
            SELECT 101, 1 UNION
            SELECT 102, 1 UNION
            SELECT 101, 3 UNION
            SELECT 103, 3 UNION
            SELECT 101, 2 UNION all
            SELECT 101, 2
            )
            –End with block
            SELECT
            ProductMaster.Product_ID,
            ProductMaster.ProductName,
            Genre.GenreName,
            ProductMaster.Price
            FROM
            ProductMaster
            left outer join Product_Genre on ProductMaster.Product_ID = Product_Genre.ProductID
            left outer join Genre on Product_Genre.Genre_ID = Genre.Genre_ID
            order by ProductMaster.Product_ID, Product_Genre.Genre_ID;

            open myCursor
            fetch next from myCursor into @pID, @pName, @pGenreCurrent, @pPrice;
            while @@FETCH_STATUS = 0
            begin
            if (@priorID = 0) or (@priorID @pID)
            begin
            print @priorName + ‘ ‘ + @pGenreTxt + ‘ ‘ + @priorPrice;
            select @pGenreTxt = @pGenreCurrent;
            end
            else
            begin
            select @pGenreTxt = @pGenreTxt + ‘ | ‘ + @pGenreCurrent;
            end
            select @priorID = @pID;
            select @priorName = @pName;
            select @priorPrice = @pPrice;
            select @priorGenreCurrent = @pGenreCurrent;
            fetch next from myCursor into @pID, @pName, @pGenreCurrent, @pPrice;
            select @pCounter = @pCounter+1;
            end;
            print @priorName + ‘ ‘ + @pGenreTxt + ‘ ‘ + @priorPrice
            close myCursor;
            deallocate myCursor;

  5. Hi pinal,

    Very useful article… i got this link when i was strucked with 2 CTE , it really helped me…
    Thank you

    regards,
    Divya

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

  6. 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

  7. 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?

  8. 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

  9. 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;

  10. 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

  11. 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

  12. 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/

  13. 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…. :)

  14. 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

  15. Pingback: SQL SERVER – Common Table Expression (CTE) and Few Observation Journey to SQLAuthority

  16. 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

  17. 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

  18. 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

  19. 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

    • 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

      • Sorry, the correct text:

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

        union all

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

  20. Pingback: SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31 « SQL Server Journey with SQL Authority

  21. Pingback: SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video « SQL Server Journey with SQL Authority

  22. 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 ;)

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  31. 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

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