Need a Translation?

PIVOT - UNPIVOT operator

Explain PIVOT and UNPIVOT operators in SQL

PIVOT and UNPIVOT are relational operators which were introduced in SQL Server 2005. These operators allow easy transformation of table-valued expression into another table.

PIVOT allows you to rotate rows into columns during the execution of an SQL Query. It performs an aggregation, and merges possible multiple rows into a single row in the output.

UNPIVOT does almost the opposite of PIVOT operator by rotating columns into rows, but it is not exactly opposite. PIVOT merges multiple rows into single while UNPIVOT fails to reproduce the original table due to the merged rows. Null input values to UNPIVOT disappear in its output.

A simple example of PIVOT operator

Suppose we have a dbo.currency_rate table which consists of three columns - in_currency_id, mn_rate and dt_currency.

-- Create table
CREATE TABLE dbo.currency_rate(in_currency_id int, mn_rate money, dt_currency datetime)

-- Insert data in table
INSERT INTO dbo.currency_rate VALUES (1, 60.15, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (2, 35.57, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (3, 78.90, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (4, 87.15, '2015-06-15 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (1, 10.18, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (2, 11.25, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (3, 08.48, '2015-06-16 09:59:16.640')
INSERT INTO dbo.currency_rate VALUES (4, 18.31, '2015-06-16 09:59:16.640')

-- Select and confirm the data before any operation
SELECT * FROM dbo.currency_rate

in_currency_id mn_rate dt_currency
1 60.15 2015-06-15 09:59:16.640
2 35.57 2015-06-15 09:59:16.640
3 78.90 2015-06-15 09:59:16.640
4 87.15 2015-06-15 09:59:16.640
1 10.18 2015-06-16 09:59:16.640
2 11.25 2015-06-16 09:59:16.640
3 8.48 2015-06-16 09:59:16.640
4 18.31 2015-06-16 09:59:16.640

Use PIVOT operator to transform rows into columns:

-- Pivot table with two rows and five columns
FOR [in_currency_id] IN ([1], [2], [3], [4])
) AS pivot_table;


dt_currency 1 2 3 4
2015-06-15 09:59:16.640 60.15 35.57 78.90 87.15
2015-06-16 09:59:16.640 10.18 11.25 8.48 18.31

A simple example of UNPIVOT operator

In continuation with the PIVOT operator example, save the result of the PIVOT operator in a temporary table:

INTO #tmp_pivot_table
FOR [in_currency_id] IN ([1], [2], [3], [4])
) AS pivot_table;

Use UNPIVOT operator to transform rows into columns:

SELECT [dt_currency], [in_currency_id], [mn_rate]
FROM #tmp_pivot_table
FOR in_currency_id IN ([1], [2], [3], [4])
) AS unpivot_table


dt_currency in_currency_id mn_rate
2015-06-15 09:59:16.640 1 60.15
2015-06-15 09:59:16.640 2 35.57
2015-06-15 09:59:16.640 3 78.90
2015-06-15 09:59:16.640 4 87.15
2015-06-16 09:59:16.640 1 10.18
2015-06-16 09:59:16.640 2 11.25
2015-06-16 09:59:16.640 3 8.48
2015-06-16 09:59:16.640 4 18.31

.Net Framework - Quick Reference Guide

The .Net Framework E-Book by the .Net Interview Cracker is a very useful resource for developers and serves well as a quick reference guide. This E-Book prepares them for technical interviews in Microsoft .Net Framework..

.Net Framework EBook for Technical Interviews

The list of topics included in the very first edition of the E-Book is as given below:
  • What is .NET Framework?
  • Explain the architecture of .Net Framework
  • Common Language Runtime (CLR)
  • Features of the Common Language Runtime
  • Dynamic Language Runtime
  • Common Language Infrastructure (CLI)
  • What is Intermediate Language (IL)?
  • How is a C# program compiled to a processor specific native code?
  • Explain the services provided by the .Net Framework
  • Assembly
  • Properties of an Assembly
  • Components of an Assembly
  • What is an Assembly Manifest?
  • Functions of the Assembly Manifest
  • Contents of the Assembly Manifest
  • Differentiate between Single-file assembly and Multifile assembly
  • What are the different types of assemblies in .NET
  • Friend Assemblies
  • Differentiate between Static and Dynamic Assemblies
  • Hub-and-Spoke model & Satellite Assemblies
  • What is DLL Hell?
  • Application Domains
  • Garbage Collection
  • Garbage Collection in detail
  • Conditions for a garbage collection
  • Benefits provided by Garbage Collection
  • Is the execution of the Garbage Collector non-deterministic? If yes, why?
  • How can you instruct the garbage collector to collect unreferenced data?
  • How can you force Garbage Collection?
  • Global Assembly Cache (GAC)
  • Mention the demerits of installation of an assembly in the GAC
  • Mention the benefits of installation of an assembly in the GAC
  • Mention the ways to deploy an assembly into the GAC
  • Where is the GAC located?
  • Enumerate the Steps for creating a Shared Assembly
The .Net Framework E-Book is now available for sale.


Details of the EBook
Name: .Net Framework - Quick Reference Guide
Author: Chandan Sinha
Format: PDF
Language: English
Price: $4.99
(Limited Period Offer - Rates slashed from $25 to $4.99)
Focus Area: Technical Interview in Microsoft .Net

.Net Framework - Quick Reference Guide [Kindle Edition]

Ebook Link on Amazon Kindle -

ASP.NET Page Life Cycle

When an ASP.NET application is executed, the web page goes through a life cycle in which it performs a series of processing steps. When a web page is requested, it is loaded into the Web Server memory, processed and sent to the web browser. Then it (the object) is unloaded from the memory. At each of these steps, methods and events are available, ready to be overridden according to the need of the application.

The Page class creates a control tree of all the controls on the page. All the components on the page, except the directives are part of this control tree. 

ASP.NET Page Life Cycle Phases
  • Initialization
  • Instantiation of controls on the web page
  • Restoration and Maintenance of state
  • Execution of the Event Handler code
  • Page Rendering

ASP.NET Page Life Cycle Stages
  • Page Request – The ASP.NET page life cycle (PLC) begins after the page is requested by a user. ASP.NET then determines whether the web page needs to be parsed and compiled, or a cached version of the page can be sent in response without running the page.
  • Start – The page properties such as Request and Response are set at the Start stage. It is determined whether the request is a postback (old request) or a new request and IsPostBack property of the Page is set accordingly. The UICulture property of the Page is also set at this stage.

      a) PreInit – Raised after the Start stage is complete and before the Initialization stage begins 
  • Initialization – The controls on the web page are available during page initialization, and UniqueID property of each control is set. If applicable, master page and themes are also applied to the page during initialization. If the current request is a postback, the postback data has not yet been loaded and control property values have not been restored to the values from view state.

      a) InitRaised after all controls have been initialized and any skin settings have been applied. The Init event of individual controls occurs before the Init event of the page.
b) InitComplete – Raised at the end of the page's initialization stage. Only one operation takes place between the Init and InitComplete events: tracking of view state changes is turned on.
View state tracking enables controls to persist any values that are programmatically added to the ViewState collection. Until view state tracking is turned on, any values added to view state are lost across postbacks. Controls typically turn on view state tracking immediately after they raise their Init event.
  • Load – During load, if the current request is a postback, control properties are loaded with information recovered from view state and control state.

      a) PreLoad – Raised after the page loads view state for itself and all controls, and after it processes postback data that is included with the Request instance. 
      b) Load – The Page object calls the OnLoad method on the Page object, and then recursively does the same for each child control until the page and all controls are loaded. The Load event of individual controls occurs after the Load event of the page.
  • Postback event handlingIf the request is a postback, the event handlers of the controls are called. After that, the Validate method of all validator controls is called, which sets the IsValid property of individual validator controls and of the page. (There is an exception to this sequence: the handler for the event that caused validation is called after validation.)

      a) Control Events – These events should be used to handle specific control events, such as a Button control's Click event or a TextBox control's TextChanged event.
      In a postback request, if the page contains validator controls, check the IsValid property of the Page and of individual validation controls before performing any processing.

      b) Load Complete – Raised at the end of the event-handling stage.

      c) PreRender – Raised after the Page object has created all controls that are required in order to render the page, including child controls of composite controls. To do this, the Page object calls EnsureChildControls for each control and for the page.
      The Page object raises the PreRender event on the Page object, and then recursively does the same for each child control. The PreRender event of individual controls occurs after the PreRender event of the page.

      d) PreRender Complete – Raised after each data bound control whose DataSourceID property is set calls its DataBind method.

      e) SaveStateComplete - Raised after view state and control state have been saved for the page and for all controls. Any changes to the page or controls at this point affect rendering, but the changes will not be retrieved on the next postback.
  • RenderingThe view state is saved for the page and all controls before rendering. During the rendering stage, the page calls the Render method for each control, providing a text writer that writes its output to the OutputStream object of the page's Response property.

      a) Render - This is not an event; instead, at this stage of processing, the Page object calls this method on each control. All ASP.NET Web server controls have a Render method that writes out the control's markup to send to the browser. A user control (an .ascx file) automatically incorporates rendering, so you do not need to explicitly render the control in code. 
  • Unload - The Unload event is raised after the page has been fully rendered, sent to the client, and is ready to be discarded. At this point, page properties such as Response and Request are unloaded and cleanup is performed.

      a) Unload - Raised for each control and then for the page. During the unload stage, the page and its controls have been rendered, so you cannot make further changes to the response stream. If you attempt to call a method such as the Response.Write method, the page will throw an exception

ASP.NET HttpHandlers – Are they really required?

ASP.NET HttpHandler is a process that runs in response to a request that is made to an ASP.NET Web application. Almost everything we do in an HttpHandler, can be done in a normal .aspx page. So, why do we need HttpHandlers? Are they really required?

Given below is the explanation of why HttpHandlers are really required:
  1. Reusability & Portability – HttpHandlers are more reusable and portable than normal .aspx pages. Since there are no visual elements in an HttpHandler, they can be easily placed into their own assembly and reused from project to project.
  2. HttpHandlers are relatively less expensive than the PageHandler. A page goes through a set of events to be processed at the server (such as onInit, onLoad, etc.), ViewState and postbacks or simply the complete Page Life Cycle. When you really have nothing to do with the complete page life cycle (like displaying images), HttpHandlers are very useful.

Handlers in ASP.NET

Handler is an agent of communication or a medium which is responsible for communication in the absence of the actual user. Technically, a Handler is a class which is responsible for Instantiation of a class i.e; allocation of memory.

Console applications or Windows applications do not have handlers. We declare a class and create an object of that class in the Main() method. We as a developer are solely responsible for handling the class – specifically - instantiation of the class.

However, Web applications have handlers. In GUI Web Applications we never create an object of the “_default” page which inherits the Page class.
In Web Services, the Web Service class is never instantiated.
In WCF, the Service class, which inherits the IService Interface is never instantiated.

Now comes the question – Who instantiates the above mentioned classes?

Behind the scene, the Handler is responsible for instantiation of the above mentioned classes. ASP.NET maps HTTP requests to HTTP handlers based on the extension of the requested file (type of file). Each HTTP handler can process individual HTTP URLs or groups of URL extensions in an application. ASP.NET includes several built-in HTTP handlers:

a)    ASP.NET – System.Web.UI.PageHandlerFactory
b)    Web Service – System.Web.Services.Protocols.WebServiceHandlerFactory
c)    WCF – System.ServiceModel.Activation.ServiceHttpHandlerFactory

There are different drivers for different protocols. The driver or API of the protocol (ex – http.sys) has to have the ability to differentiate and resolve requests.
.sys files are the drivers for protocols. http.sys file is a driver or API or listener for the HTTP protocol. You can say that HTTP is abstraction, while http.sys is encapsulation. http.sys file handles the request and response in HTTP. The http.sys file is located in C:\Windows\System32\drivers\ folder.

When a request arrives on the server, the drivers for the protocols (.sys files) handles that request and forwards that request to a particular handler according to the extensions.

Conversion of Seconds to HH:MM:SS format

Recently I faced a requirement of converting seconds to HH:MM:SS format. After some R&D, I found the following solution:


DECLARE @in_seconds int

SET @in_seconds = 3661 -- One Hour One Minute and One Second

SELECT CONVERT(CHAR(8), DATEADD(SECOND, @in_seconds, 0), 108) As Hour_Minute_Second



Note: This SQL code is applicable only for time less than 24 hours.

To overcome this limitation of 24 hours, I created the following function, which has the ability to return correct time duration for large time duration in seconds:

USE [AdventureWorks]
/****** Object:  UserDefinedFunction [dbo].[fnc_convert_seconds_to_HHMMSS]    Script Date: 10/02/2013 08:48:33 ******/

CREATE FUNCTION [dbo].[fnc_convert_seconds_to_HHMMSS]
@dc_time decimal(18,2)



RETURN REPLACE(STR(CONVERT(INT,@dc_time/3600), LEN(LTRIM(CONVERT(INT,@dc_time/3600)))) + ':' + STR(CONVERT(INT,(@dc_time/60)%60), 2) + ':' + STR(@dc_time%60, 2), ' ', '0')

Example: SELECT dbo.fnc_convert_seconds_to_HHMMSS(36460) AS vc_time_in_HHMMSS

Output: 10:07:40