set based approach

Generate serial number for each consecutive set of numbers

In this post, we are going to generate serial number for each consecutive set of numbers in a given column. We have a table tbl_Donation with columns EmpId and DonationYear. For each consecutive set of years of each employee, we need to generate serial numbers. If employee “X” donated in 2006, 2007 and 2008 years consecutively and […]

Generate serial number for each consecutive set of numbers Read More »

Count of total spaces in a string in SQL Server – Various methods

In this post we are going to find the total number of spaces in a given string. I have a string which contains spaces at multiple places and i just want to count them all. We can achieve this in various ways. Here, i am sharing some of these methods. Why i am doing this

Count of total spaces in a string in SQL Server – Various methods Read More »

Convert date range into date list in SQL Server

In this post “Convert date range in date list”, we will convert a given date of range into list of dates as per the business requirement. Just have a look on mentioned demo table which has three columns CustomerID, StartDate and EndDate. I want to generate the date list for the given date range for each customer.

Convert date range into date list in SQL Server Read More »

Read and compare XML nodes dynamically with unknown elements

In this article, I will share a practical example from my experience. In one of my project, I had a requirement of dynamic data comparison between two entities and the number of attributes were dynamic and can be added any time by end user and removed too. Let me explain it with an example that how we

Read and compare XML nodes dynamically with unknown elements Read More »

Row mapping and generating equivalent records for given condition in sql server

In this article, i will demonstrate a solution for dynamic row mapping problem in sql server. We will see “row mapping for given condition” with examples and in various way. One of my colleague asked me a question as below; “I have a table with Male and Female genders and want an output by mapping each Male with

Row mapping and generating equivalent records for given condition in sql server Read More »

Generate Defined number of rows dynamically (SET based approach)

In this article , we will generate as much number of rows as needed for each row of given table dynamically. We have a table named #Employee with two columns “EmpName” and “RowsToGenerate”. Lets explore “Generate rows dynamically” topic in with examples and demos. Generate Rows dynamically as per given count: Below is the structure of the table #Employee

Generate Defined number of rows dynamically (SET based approach) Read More »

Convert a comma separated list into table

In this post, we are going to learn “how we can convert a comma separated list into a table using a recursive CTE”. Below is the code: Why OPTION MAXRECURSION? Hint OPTION(MAXRECURSION 32767) is a query hint which instructs to compiler to iterate 32767 times. Default, max recursion of a loop in SQL is 100 and in case we

Convert a comma separated list into table Read More »

Avoid Use of While Loops to get a Comma Seperated list of rows

Problem Detail In the morning at your workplace, your manager or any colleague comes to you and asks to get the list of all the dates in a comma separated list. Now the first and the quickest way of getting the output which will knock us is using a loop to get the list. In

Avoid Use of While Loops to get a Comma Seperated list of rows Read More »