SQL Tip #5 – PIVOT Table

We write sql queries in order to get different result sets like full, partial, calculated, grouped, sorted etc from the database tables. However sometimes we have requirements that we have to rotate our tables. Sounds confusing?

Let’s keep it simple and consider the following two screen grabs.

SQL Table:

Expected Results:

Wow, that’s look like a lot of work! That is a combination of tricky sql, temporary tables, loops, aggregation……, blah blah blah

Don’t worry let’s keep it simple, stupid(KISS).

MS SQL Server 2005 and above has a function called PIVOT. It s very simple to use and powerful. With the help of this function we will be able to rotate sql tables and result sets.

Simple steps to make it happen:

  1. Identify all the columns those will be part of the desired result set.
  2. Find the column on which we will apply aggregation(sum,ave,max,min etc)
  3. Identify the column which values will be the column header.
  4. Specify the column values mentioned in step3 with comma separated and surrounded by square brackets.

So, if we now follow above four steps and extract information from the above sales table, it will be as below:

  1. Year, Month, SalesAmount
  2. SalesAmount
  3. Month
  4. [Jan],[Feb] ,[Mar] …. etc

We are nearly there if all the above steps made sense to you so far.

Now we have all the information we need. All we have to do now is to fill the below template with required information.
Template:

Our SQL query should look like below:

SELECT *
FROM
(
    SELECT SalesYear, SalesMonth,Amount
    FROM Sales
 ) AS SourceTable
PIVOT
(
       SUM(Amount )
       FOR SalesMonth
       IN ( [Jan],[Feb] ,[Mar],
            [Apr],[May],[Jun] ,[Jul],
            [Aug],[Sep] ,[Oct],[Nov] ,[Dec])
) AS PivotTable;

In the above query we have hard coded the column names. Well it’s not fun when you have to specify a number of columns.

However, there is a work arround as follows:

DECLARE @columnList nvarchar (MAX)
DECLARE @pivotsql nvarchar (MAX)

SELECT @columnList =   STUFF(
             (
               SELECT ',' + '[' + SalesMonth + ']'
               FROM Sales
               GROUP BY SalesMonth
               FOR XML PATH( '')
           )
        ,1, 1,'' )

SET @pivotsql =
       N'SELECT *
      FROM
      (
            SELECT SalesYear, SalesMonth,Amount
            FROM Sales
       ) AS SourceTable
      PIVOT
      (
           SUM(Amount )
           FOR SalesMonth
           IN ( ' + @columnList +' )
      ) AS PivotTable;'

EXEC sp_executesql @pivotsql

Hopefully this tutorial will be a help to someone somewhere.
Enjoy coding.

SQL Tip #4 – Pagination

Presenting a small subset of relevant information to the user at a time is very important. This type of small presentation of data is very important, otherwise user will be overwhelmed with a lot of information in front of them, and also by fetching all the information in one go will create pressure on the application  and as well as on the underlying database. As a result the end user will ended with the negative experience.

With the help of pagination, contents can be divided into different pages, and then retrieval of any given pages can be achieved efficiently with optimal performance.

In order to implement pagination, one of the following stored procedures could be used:

SQL 1:

	CREATE PROC [dbo].[proc_Employee_Select]
	(
		@pageNo int,
		@pageSize int
	)
	AS
	BEGIN

	DECLARE @StartPosition INT
	DECLARE @EndPosition INT

	SET @StartPosition = ((@pageNo - 1) * @pageSize )+ 1
	SET @EndPosition = @pageNo*@pageSize 

		SELECT EmployeeId,EmployeeName,Salary FROM
		(
			SELECT ROW_NUMBER()
			       OVER( ORDER BY  EmployeeId) AS Rid, *
			       FROM Employee 

		) Wrapper

		WHERE Wrapper.Rid BETWEEN @StartPosition AND @EndPosition
	END

SQL 2:

	CREATE PROC [dbo].[proc_Employee_Select_2nd]
	(
		@pageNo int,
		@pageSize int
	)
	AS
	BEGIN

		DECLARE @StartPosition INT
		DECLARE @EndPosition INT

		SET @StartPosition = ((@pageNo - 1) * @pageSize )+ 1

		DECLARE @EmpId INT
		SELECT TOP (@StartPosition) @EmpId = EmployeeId FROM Employee

		SELECT TOP (@pageSize) * FROM Employee
		WHERE EmployeeId>=@EmpId
		ORDER BY EmployeeId

	END

SQL 3:

	CREATE PROC [dbo].[proc_Employee_Select_3rd]
	(
		@pageNo int,
		@pageSize int
	)
	AS
	BEGIN
		
		DECLARE @StartPosition INT
		DECLARE @EndPosition INT
		
		SET @StartPosition = ((@pageNo - 1) * @pageSize )+ 1


		DECLARE @EmpId INT
		SET ROWCOUNT @StartPosition
		SELECT @EmpId = EmployeeId FROM Employee
		
		SET ROWCOUNT @pageSize
		SELECT * FROM Employee
		WHERE EmployeeId>=@EmpId
		ORDER BY EmployeeId
		SET ROWCOUNT 0

	END

SQL Tip #3 – Odd and Even records

If you ever need to display odd and even records from the MS SQL Server database, following sql queries could be a help.

Sample Table:

EmployeeTable

Odd Rows

SQL 1:

SELECT Main.* FROM Employee Main
CROSS APPLY
(
    SELECT COUNT(EmployeeId) AS Ctr
    FROM Employee
    WHERE EmployeeId <= Main.EmployeeId
) AS Sub
WHERE Ctr % 2 <> 0
ORDER BY Main.EmployeeId

SQL 2:

SELECT EmployeeId,
EmployeeName,
Salary
FROM
(
    SELECT ROW_NUMBER()
    OVER (ORDER BY EmployeeId) AS RowId,*
    FROM Employee
) AS Wrap
WHERE RowId % 2 <> 0

 

Even Rows

SQL 1:

SELECT Main.* FROM Employee Main
CROSS APPLY
(
     SELECT COUNT(EmployeeId) AS Ctr
     FROM Employee
     WHERE EmployeeId <= Main.EmployeeId
) AS Sub
WHERE Ctr % 2 = 0
ORDER BY Main.EmployeeId

SQL 2:

SELECT EmployeeId,
 EmployeeName,
 Salary
FROM
(
     SELECT ROW_NUMBER()
     OVER (ORDER BY EmployeeId) AS RowId,*
     FROM Employee
) AS Wrap
WHERE RowId % 2 = 0

 

In both the cases SQL 2 performs well.

SQL Tip #2 – Running total

Today it is about running total.

Running totals are sum of values that grows as new line item is introduced in a table. Or simply, we can say – a total that continually changes for added rows. This is also called partial sum. Running total in a row is calculated by adding a value from the current row to the running total of the previous row.

Consider the example below:

Running total Salary = Current Salary + Previous row’s running total Salary

RunningTotal

Like my all other sql tips, today also I am going to show you different  ways  to achieve this :

SQL 1:

SELECT TOP 1000
 Main.*,
 RunningTotal = SUM(Sub.Salary)
FROM
 Employee AS Main
INNER JOIN
 Employee AS Sub
 ON Sub.EmployeeId <= Main.EmployeeId
GROUP BY Main.EmployeeId,Main.EmployeeName,Main.Salary
ORDER BY Main.EmployeeId

SQL 2:

SELECT TOP 1000 *,
(
    SELECT SUM(Salary)
    FROM Employee
    WHERE EmployeeId <= MainEmp.EmployeeId
 ) AS RunningTotal
FROM Employee MainEmp

SQL 3:

SELECT TOP 1000 MainEmp.*,SubEmp.Salary AS RunningTotal
FROM Employee MainEmp
CROSS APPLY
(
     SELECT SUM(Salary) AS Salary
     FROM Employee
     WHERE EmployeeId <= MainEmp.EmployeeId
) AS SubEmp

 

SQL 4:

SELECT TOP 1000  Main.*,
       RunningTotal = Salary + ISNULL(
 (
    SELECT SUM(Salary)
    FROM Employee AS Sub
    WHERE Sub.EmployeeId < Main.EmployeeId), 
 0)
FROM Employee AS Main
ORDER BY EmployeeId

To be continued….