Delete all duplicate rows except one from a SQL Server table

Delete all duplicate rows except one from a SQL Server table

All the duplicate rows from below table should be deleted except one.

After delete operation table should look like this:

First we need to figure out the duplicate rows  and to get it done we will add one extra column whose value will be generated using ROW_NUMBER ( ) inbuilt function with Partition By , so that row number will be generated for each partition, Basically it should look like as shown below.

After generating the row_number for each groups, we will create a CTE and Delete all the rows having row number >1 (duplicate rows) .

Final Query:

WITH EmployeeCTE AS

(

SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) 

AS RowNumber

from Employee

)

DELETE FROM EmployeeCTE WHERE RowNumber >1

Query for listing DeptNo, ename, sal, SUM(Total sal in that department)

Query for listing DeptNo, ename, sal, SUM(Total sal in that department)

In the above scenario it is asked to list DeptNo, ename, sal along a new column containing sum of salary of individual dept.

Suppose we have a table EMP, lets query the required column.

select DEPTNO,ENAME,SAL from EMP ORDER BY DEPTNO;

Here we can't apply GROUP BY clause directly because we have to print ENMAE and SAL too along with total salary belong to that department.











Lets write the final query.

SELECT a.DEPTNO, ENAME, SAL, (SELECT SUM(SAL) FROM EMP e WHERE a.DEPTNO = e.DEPTNO) as DeptSal FROM EMP a
ORDER BY a.DEPTNO;

Write a query to get the product of all the values of a column in SQL Server

Write a query to get the product of all the values of a column in SQL Server

Idea is little bit tricky but very easy to understand. Since there is no any multiplication function like other aggregate functions in SQL Server, we need create a function which will behave like multiplication function using existing functions. Before writing the query I would strongly recommend you to understand how we use Log( ) function and exponential function

In Mathematics,

log x + log y = log(x * y)

and exponential function is exactly reverse of log. i.e. e^logx = x  so e^log(x * ^y) = x * y

Suppose we have a table containing two rows as shown below and we want product of all the rows in a particular column.

Salary

100

50

100*50 = 5000 (Result)

here, we are taking log of each record and then doing the summation

log (100) + log (50)= log (100 * 50)

 Now, we will take exponential of above result.

e^{log(100 * 50)}  = 100*50

Summarized: Sum the log values and take the exponential to get the product of values, EXP(SUM(log(Values))).

SELECT EXP(SUM(log(salary))) FROM EMPLOYEE

Second method:

DECLARE @a VARCHAR(100)

SELECT @a = COALESCE(@a+' * ' , '') + CAST(Salary as varchar(100))  from #Test

--PRINT @a

EXEC ('SELECT ' + @a)

Retrieve nth row from a table in SQL Server

Retrieve nth row from a table in SQL Server

In below example I am going to explain in two way. For below explanation I have taken n=6.

Method: 1

WITH Records AS

(

select *,row_number() OVER(order by empid) as TempEmployee from employee

) 

select * from records where TempEmployee=5 

We are creating a CTE and selecting all the columns including an extra column which is going to be generated using ROW_NUMBER( ) inbuilt function, which will generate row number for each row.

After that we are selecting the required row from the created CTE

Method: 2 

SELECT * FROM (SELECT *, row_number() OVER(order by empid) AS RowNum from employee) Resultset

WHERE RowNum = 5

In above query, the inner query will give all the columns including an extra column using ROW_NUMBER ( ) function and later as part of outer query we are filtering using WHERE clause for required row number.

Query to Print 1 to 100 in SQL Server without using Loops.

Query to Print 1 to 100 in SQL Server without using Loops.

There are several ways to achieve above scenario in SQL Server. Here I will explain using Recursive CTE.

WITH RESULTS

AS

(

   SELECT 1 SequenceNo

   UNION ALL

   SELECT SequenceNo+1 from RESULTS WHERE SequenceNo < 100

)

SELECT * from RESULTS

 

Generate Row Number in SQL Server without using ROW_NUMBER( ) Function

Generate Row Number in SQL Server without using ROW_NUMBER( ) Function

Generating a row number for each row in SQL Server without ROW_NUMBER ( ) function is little bit tricky. The method which I am going to explain below is not even generic. this method only works if there is at least one unique column in the table (or collection of columns should be unique).

SELECT empid, empname, salary, (SELECT COUNT(*) FROM EMPLOYEE i WHERE o.empid >= i.empid) as Row_Num

FROM employee o order by Row_Num

The Column (empid) which we have used in above query for row number generation is called "sort key". For this technique to work , the sort key needs to be unique. I have chosen the column "empid" because this column contains unique value in our Employee table. If it was not Unique but some other collection of columns was, then we could have used those columns as our sort key  (by concatenating those columns to form a single sort key).

Also notice how rows are sorted in the result set. We have done an explicit sorting on the 'Row_Num' column, which gives us all the row numbers in the sorted order. But notice that 'empid' column is also sorted which is probably the reason why this column is referred as sort-key.

It's very easy to achieve above scenario using ROW_NUMBER ( ) inbuilt function as shown below.

SELECT empid, empname, salary, ROW_NUMBER() OVER (ORDER BY empid) as Row_Num FROM employee

How to generate Sequence without using Ranking Function in SQL Server ?

A
1
1
3
5
7
9
11
13
15
17
19
21

Expected output:

RN	A
1	1
2	1
3	3
4	5
5	7
6	9
7	11
8	13
9	15
10	17
11	19
12	21

SELECT COUNT(*) OVER(ORDER BY A, CONCAT(A,NEWID())) Row_Num, A FROM #temp

Converting Comma separated values to Rows and Vice Versa in SQL Server(Method 1).

Converting Comma separated values to Rows and Vice Versa in SQL Server(Method 1).



Introduction:

While doing ETL Testing or while Reporting you may encounter a situation where you will have comma separated (or separated with some other character) values and you want to report them in rows of a table or vice-versa.

In this article, I am going to explain how to write SQL query in SQL Server to handle this scenario.

Scenario: 1(Converting comma separated values in a cell to different rows)

For converting a comma separated value to rows (as shown in above picture), I have written a user defined function to return a table with values in rows. It takes comma separated values as the input parameter, iterates through it as long as it finds a comma in the value, takes each value before the comma, inserts into a table and finally returns the inserted data from that table.
It makes use of CHARINDEX inbuilt function to search for the existence of commas in the input parameter and returns the starting position of comma. It does as long as the comma position is greater than zero. Then it makes use of the STUFF inbuilt function to replace a part of the main input parameter (which has already been inserted into the table) with a zero length string; effectively removing the value before comma, which has already been extracted from the main input parameter and inserted into the table. It also uses LTRIM and RTRIM inbuilt functions to remove any extra spaces from the beginning or end of the value if there are any.

/*User Defined Function*/

CREATE FUNCTION dbo.BreakStringIntoRows (@CommaSeperatedString varchar(1000))

RETURNS @Result TABLE (Column1 VARCHAR(100))

AS

BEGIN

     DECLARE @DelimiterLocation INT

     WHILE (CHARINDEX(',', @CommaSeperatedString, 0) > 0)

     BEGIN

          SET @DelimiterLocation = CHARINDEX(',', @CommaSeperatedString, 0)

          INSERT INTO @Result (Column1)

          /*LTRIM and RTRIM to ensure blank spaces are removed*/

          SELECT RTRIM(LTRIM(SUBSTRING(@CommaSeperatedString, 0, @DelimiterLocation)))  

          SET @CommaSeperatedString = STUFF(@CommaSeperatedString, 1, @DelimiterLocation, '')

          END

          /*Below step is for inserting last value into the table since above part of query will not insert last record into the table*/

          INSERT INTO @Result (Column1)

          SELECT RTRIM(LTRIM(@CommaSeperatedString)) /*LTRIM and RTRIM to ensure blank spaces are removed*/

          RETURN

END

GO

/*Using the User Defined Function to convert comma separated values into rows*/

SELECT * FROM dbo.BreakStringIntoRows('Dog,Cat,Cow') --Without space

SELECT * FROM dbo.BreakStringIntoRows('Dog , Cat, Cow')

--With spaces in between the values

Scenario: 2(Converting Rows to Comma separated values)

Before explaining how to achieve this let me create the table and insert some values

/*Create a table containing values in rows*/

 
CREATE TABLE Animals
(  Name VARCHAR(50))GO

/*Insert Values in the rows*/
INSERT Animals(Name) VALUES ('Dog')

INSERT Animals(Name) VALUES ('Cat')

INSERT Animals(Name) VALUES ('Cow') GO
 

Now we have values in rows and to convert them to a single comma separated value, we can use the script below, which uses the COALESCE inbuilt function.

The COALESCE function takes a list of parameters, separated by commas, evaluates them and returns the value of the first of its input parameters that is not NULL.

Though COALESCE and ISNULL functions have a similar purpose, they can behave differently. For example, ISNULL function is evaluated only once whereas the input values for the COALESCE function can be evaluated multiple times or until it reaches to the first not-NULL value to return.

DECLARE   @ConcatString VARCHAR(2000)

SELECT   @ConcatString = COALESCE(@ConcatString + ', ', '') + Name FROM Animals

SELECT   @ConcatString AS Animals

GO



OR

DECLARE   @NAME VARCHAR(MAX)=''

SELECT   @NAME = @NAME + Name + ',' FROM Animals

SELECT   SUBSTRING(@NAME,1,LEN(@NAME)-1) AS Animals

GO