Click here to Skip to main content

Submit your article

CTE

CTE

Great Reads

Non Recursive CTEs Explained

by essentialSQL

In this article, we explore non recursive CTEs (Common Table Expressions). This is a broad class, and basically covers every form of CTEs except those that call themselves. This other class is called the recursive CTEs; they are covered in the next article.

Getting a list of SQL Server modules to refresh

by PIEBALDconsult

Using a Common Table Expression to produce a list of Views, Procedures, and Functions

Stupid CTE tricks -- string concatenation

by Jörgen Andersson

Pretty cool little trick you've made there.With the help of an analytic function, I've fixed the ordering, and as a byproduct it also scales better:WITH ranked AS ( SELECT make,model,Rank() over (PARTITION BY make ORDER BY model) Rnk FROM MakeModel ),cte...

Getting a List of SQL Server Modules to Refresh

by PIEBALDconsult

How to get a list of SQL Server modules to refresh

Latest Articles

Non Recursive CTEs Explained

by essentialSQL

In this article, we explore non recursive CTEs (Common Table Expressions). This is a broad class, and basically covers every form of CTEs except those that call themselves. This other class is called the recursive CTEs; they are covered in the next article.

Group concatenation in T-SQL using CTE

by Johan Ohlin

Group concatenation in T-SQL using CTE.

How to use recursive CTE calls in T-SQL

by Johan Ohlin

How to use recursive CTE calls in T-SQL.

Getting a List of SQL Server Modules to Refresh

by PIEBALDconsult

How to get a list of SQL Server modules to refresh

All Articles

CTE

Non Recursive CTEs Explained

14 Jul 2016 by essentialSQL

In this article, we explore non recursive CTEs (Common Table Expressions). This is a broad class, and basically covers every form of CTEs except those that call themselves. This other class is called the recursive CTEs; they are covered in the next article.

Getting a list of SQL Server modules to refresh

25 Dec 2011 by PIEBALDconsult

Using a Common Table Expression to produce a list of Views, Procedures, and Functions

Stupid CTE tricks -- string concatenation

7 Jan 2012 by Jörgen Andersson

Pretty cool little trick you've made there.With the help of an analytic function, I've fixed the ordering, and as a byproduct it also scales better:WITH ranked AS ( SELECT make,model,Rank() over (PARTITION BY make ORDER BY model) Rnk FROM MakeModel ),cte...

creating stored procedure for 'th' highest marks

17 May 2012 by Wendelius

The procedure itself is quite straightforward, modify the statement as per requirements and then include inside a procedure, see CREATE PROCEDURE [^]About the query, I would advice to separate the first column to two separate columns if the data describes different things. Concatenated...

SQL-Server-2005

stored-procedure

creating stored procedure for 'th' highest marks

17 May 2012 by VJ Reddy

If the requirement is to retrieve nth row from the Table then, the ROW_NUMBER function of Sql Server can be used for this purpose as shown belowCREATE PROCEDURE GetnthRow @nthRow nvarchar(30)ASWITH OrderedMarks AS( SELECT ROW_NUMBER() OVER (ORDER BY stdName) as RowNo,...

SQL-Server-2005

stored-procedure

Weekly Item Count in SQL Server

13 Mar 2013 by Mike Meinz

You did not specify the unit of measure for the count. I used MINUTE in the example below. Using DATEDIFF, you can also specify other units of measure. See DATEDIFF (Transact-SQL)[^]SELECT ItemId,ISNULL(AssignmentName,"") As...

How to acheive result set basing on cte's and levels

31 Mar 2015 by CHill60

This article[^] from Pinal Dave explains how to use Recursive CTEs to produce the Hierarchical results that you are after.I came back to this to give a fuller explanation in light of your specific question as it can also be achieved using nested CTEs.I decided that my anchor statement...

SQL-Server-2008R2

show previous quarter avg sale with current quarter avg sale

1 Sep 2015 by Maciej Los

Try this:SELECT (SELECT AVG(Sales) FROM product WHERE year = @prevYear AND @QTR=@prevQtr) AS PrevQtr, (SELECT AVG(Sales) FROM product WHERE year = @currYear AND @QTR=@currQtr) AS CurrQtrReplace @prevYear, @prevQtr, @currYear and @currYear with corresponding values.Another...

How do I solve the problem with my query?

8 May 2016 by Tomas Takac

To get a list of jobs that were running during the execution of another job you can use a simple join:declare @job varchar(100) = 'main'select b.* from COMP_HIS_TBL ainner join COMP_HIS_TBL bon b.Start_Time = a.Start_Timeand b.Job_name ...

Help me to achieve this result of query like below given output

12 Feb 2020 by Maciej Los

You can use CTE[^] for such of requirement. See: DECLARE @Subjects TABLE ( SubjectId INT PRIMARY KEY IDENTITY(1,1), SubjectText VARCHAR(70), ParentSubjectId INT ); INSERT INTO @Subjects (SubjectText, ParentSubjectId) VALUES...

Getting a List of SQL Server Modules to Refresh

26 Apr 2012 by PIEBALDconsult

How to get a list of SQL Server modules to refresh

creating stored procedure for 'th' highest marks

17 May 2012 by nischalinn

I've a tablecreate table marks(stdName nvarchar(30), marks float)insert into marksselect 'std1', 98union all select 'std2', 96union all select 'std3', 95union all select 'std4', 97union all select 'std5', 93I want to create stored procedure to select the row with 'th' highest...

SQL-Server-2005

stored-procedure

Merge Parent and Child Tables

11 Sep 2014 by RAHUL(10217975)

I have 3 Tables i.e.Categorys CatID 01 02 03 04 05 06 07CatName A B C D E F G Products ProdID 01 02 03 04 05 06 07 ProdName P Q R S T U VCat-ProdCatID 01 02 02 03 05 06 ProdID 01 02 03 04 04 04Every Product can have multiple categories ..So...

Merge Parent and Child Tables

12 Sep 2014 by Maddy selva

select a.CatName,c.ProdName from Categorys as a inner join [Cat-Prod] as b on a.CatID=b.CatID inner join Products as c on b.ProdID=c.ProdIDOutputCatName ProdNameA pB qB rc se sf s

How to acheive result set basing on cte's and levels

31 Mar 2015 by Member 11283674

Hi Guys,Today I faced a strange question in interview.I can't even have any idea of it how to doQuestion is I have a student table.Create table #student(sname varchar(20),medium varchar(20),class varchar(20))insert into...

SQL-Server-2008R2

show previous quarter avg sale with current quarter avg sale

3 Sep 2015 by Jörgen Andersson

You have two problems that needs to be solved here.Your table definition sucks.You have three different temporal types that doesn't sort properly.You have gaps in the sequence.So lets fix them.first we need to fix the temporal types SELECT region ,Product ...

show previous quarter avg sale with current quarter avg sale

6 Sep 2015 by Member 11898509

with qtrs(qtr) as (select level from dual connect by level

Need Help in Recursive Query in SQL using CTE

14 Sep 2015 by Member 11983027

I need help in sql recursive query, for example purpose i m providing sample table with insert script.CREATE TABLE Details(parentid varchar(10), DetailComponent varchar(10) , DetailLevel int)GOINSERT INTO Details SELECT '','7419-01',0 union allSELECT '7419-01','44342-00',1...

SQL-Server-2012

Need Help in Recursive Query in SQL using CTE

14 Sep 2015 by jaket-cp

This is not a solution in itself, as the comment is not really adequate.Here is a recursive cte for the parent child relationship.with DetailData as ( SELECT '' ParentID, '7419-01' DetailComponent, 0 DetailLevel union all SELECT '7419-01','44342-00',1 union all SELECT...

SQL-Server-2012

Need Help in Recursive Query in SQL using CTE

14 Sep 2015 by Wendelius

Not sure if I understand the question correctly, but if you need to find the parent components based on the given component id, perhaps something like this:WITH BOM (ParentComponent, ChildComponent, DetailLevel) AS ( SELECT a.parentid AS ParentComponent, a.DetailComponent AS...

SQL-Server-2012

Need Help in Recursive Query in SQL using CTE

14 Sep 2015 by Magic Wonder

Hi,Check this....Recursion in SQL using CTE[^]hope this will help you.Cheers

SQL-Server-2012

Query to group and sort hierarchical data

17 Nov 2015 by Yesudass Moses

I have a table like this in my SQL Server 2008. ID ParentID Level Code Name Description1 1 1 EXP Expenses --2 1 2 PEXP Project Exp --3 1 2 IEXP Indirect Exp. --4 4 ...

How do I solve the problem with my query?

8 May 2016 by mousau

Hi All Needed help regarding a query.The query needs to display the concurrency list for jobs. The table has the following fields:-Job_Name | Start_time | End_time | Execution_time | statusI got to display the list of those jobs which started at a time lesser than the start time of...

Retrieving hierarchical table data as simple table format for report? Help please!

25 Aug 2016 by CringTee

Resolved using CTE and by referring Dynamic Pivot Query in SQL Server[^] "Dynamic Pivot Query in SQL Server"

SQL-Server-2008R2

stored-procedure

Convert SQL server 2012 query to SQL 2008

8 Sep 2016 by ashishkumarrai

I have the below query which works fine in 2012 as it is using the 2012 features. Can anyone make the exact same query work in 2008 with same out put?declare @Buckets table (ID char(2), FullCapacity int)declare @Filler table (ID char(2), Filler int)insert into @Buckets values('B1',...

T-SQL single row corresponding rows

7 Jun 2018 by Greg Crossan

I have a SQL table similar to this (MSSQL 2017 Standard) Key Period Year Srce Destination Amt 001 001 2018 A B 100 002 001 2018 A C 200 003 001 2018 B A 99 004 001 2018 C A 180 Initial...

T-SQL single row corresponding rows

6 Jun 2018 by Member 7870345

Please try SELECT period, year, CASE WHEN srce

T-SQL single row corresponding rows

7 Jun 2018 by Maciej Los

Seems you need to combine data based on period, year and source/destination SELECT t1.Period AS Prd, t1.Year AS Year1, t1.Srce AS Src1, t1.Amt AS SourceAmount, t1.Desttination AS Dest1, t2.Amt - t1.Amt AS Variance, t2.Srce As Src2, t2.Destination AS Dest2, t2.Period, t2.Year AS Year2,...

How to get day differences and fiscal date ranges by providing start date and end dates

7 May 2019 by iAnkitBhatt

i am stuck at the point. i need help for day difference calculation for financial application. 1. two dates as input. 2. the date range will be classified as per Indian financial years (April to march) 3. first range will start from start date. and last generated range will be till End date...

How to get day differences and fiscal date ranges by providing start date and end dates

2 May 2019 by #realJSOP

I wrote a tip/trick that features a stored proc which lets you build a calendar that includes fiscal dates. Build a Calendar Without Pre-Existing Tables[^] In that code, the fiscal year starts on October 01, so the fiscal offset is 3. So if your fiscal start is JUN 01, the offset would be 8 (I...

How to get day differences and fiscal date ranges by providing start date and end dates

7 May 2019 by iAnkitBhatt

Dear All Thanks for your Responses... Achieved Workable solution for now... please revert if any optimization or effective is available by Select query only... Query: DECLARE @MinDate DATE = '2015-12-12', @MaxDate DATE = '2018-12-12'; SELECT FT.Row_Number,FT.Endfiscalyear-1 as...

Conversion of CTE to temp table in SQL to get all possible parents

10 Sep 2019 by Member 7673567

I have one user table in which i maintain parent child relationship and I want to generate the result with all user id along with its parentid and all possible Hierarchical parents as coma separated strings, my table structure is as follows. CREATE TABLE [hybarmoney].[Users]( [ID] [bigint]...

Conversion of CTE to temp table in SQL to get all possible parents

10 Sep 2019 by Maciej Los

Please, read my comment to the question first. I believe there's something wrong with your CTE, because when you set MAXRECURSION option to 0 (zero), it enable to create inifinite loop. Maximimum value for MAXRECURSION is 32767. Below CTE is properly executed, even if it repeats almost 40000...

Can anyone help me with code optimization on my T-SQL 2K16 query?

14 Sep 2020 by Sys Dev

Hi. I have used CTE extensively in my scripts and just lately I found that a new script is really slow to return a result set. I have been really dumbfounded on how to further optimize as I have found that CTE is the best method in querying...

Create recursive CTE for this table

19 Nov 2022 by OriginalGriff

If you don't understand an error message, google it: The statement terminated. The maximum recursion 100 has been exhausted before statement completion. - Google Search[^] Then follow a few links, like this one: The maximum recursion 100 has...

How to use recursive CTE calls in T-SQL

15 Nov 2013 by Johan Ohlin

How to use recursive CTE calls in T-SQL.

SQL-Server-2012

Stupid CTE tricks -- string concatenation

25 Dec 2011 by PIEBALDconsult

Concatenating values from multiple rows into one string value via a Common Table Expression

Group concatenation in T-SQL using CTE

15 Nov 2013 by Johan Ohlin

Group concatenation in T-SQL using CTE.

SQL-Server-2012

Query to group and sort hierarchical data

18 Nov 2015 by Sums Mohs Eds

Hi....As per the table structure, inorder to get the desired output, I think there is a need to change the table structure and the way records are organized.Following are the changes:-1) Keep the parentid as null for the records, which is equal to id i.e. Parentid equal to ID, in this...

Convert SQL server 2012 query to SQL 2008

8 Sep 2016 by Alex Banu

I found this :Quote:This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY clause with aggregate functions like MINmore detailsYou don't have a SQL 2008 here, but considering the above mentioned, i think you can...

Retrieving hierarchical table data as simple table format for report? Help please!

18 Aug 2016 by Richard Deeming

Your sample data doesn't make any sense - objective 3 has no criteria, and criteria 5 has no procedure.Assuming that's just a typo in your question, something like this should work:SELECT O.Description As Objective, C.Description As Criteria, P.Description As...

SQL-Server-2008R2

stored-procedure

show previous quarter avg sale with current quarter avg sale

3 Sep 2015 by Member 11898509

I have a table one in which there are various attribute like region product,year,qtr,month,sale. I have to calculate the avg_qtr sale of each product having same region and show their previous avg_qtr sale.I have read about lag but here it is not possible to use as it is not fixed after how many...

Weekly Item Count in SQL Server

13 Mar 2013 by mail4bartley

Trying to get a count of how long an item is open on a weekly basis. So if it opens and closes I want to know how long it was open. If close date is null I want to use GetDate()CREATE TABLE [dbo].[TestDates]( [ItemId] [int] IDENTITY(1,1) NOT NULL, [AssignmentName] [nvarchar](max)...

How can we use CTE in sql?with example

21 Sep 2014 by PeterPaul92

How can we use CTE in sql?with example

How can we use CTE in sql?with example

21 Sep 2014 by Thanks7872

See this : How to use cte in sql[^]Let me know if any of that 3,25,000 results don't help you.

Retrieving hierarchical table data as simple table format for report? Help please!

25 Aug 2016 by CringTee

Dear experts,I have a hierarchical table which stores data like below in SQL Server 2008 database.ID Description Category ParentID1 ABC Objective NULL2 CDE Objective NULL3 XXX Objective NULL4 YYY Criteria 15 DDD Criteria 16 AAA ...

SQL-Server-2008R2

stored-procedure

Help me to achieve this result of query like below given output

12 Feb 2020 by Test Test

help me to achieve this result Output has only 2 columns 1. Text: Displayed in image 2. Id: ID of the last subject (last child) OUTPUT: Name Id Accountancy > Payroll > Sage Payroll ...

Create recursive CTE for this table

19 Nov 2022 by houman farokhi 2022

0 I have a table like this: |id |name |parent| +-------+----------+------+ |1 |iran | | |2 |iraq | | |3 |tehran |1 | |4 |tehran |3 | |5 |Vaiasr St |4 | |6 |Fars ...