Monday 15 December 2014

DISTINCT in case insensitive SQL Server

SolutionTo illustrate this behavior we are going to look at a couple ways this works using a case sensitive database and a case insensitive database.
The first set of queries uses the AdventureWorks database which is configured as case sensitive.  To determine the collation for your databases you can run this query:
SELECT name, collation_name
FROM master.sys.databases
We are querying the data from Preson.Contact in the AdventureWorks database.  All data is setup as mixed case, so we have no duplicates when we run this query.
SELECT DISTINCT TOP 10 FirstName
FROM Person.Contact
WHERE FirstName LIKE 'A%'
ORDER BY 1
If we update one of the record and change the FirstName from "Adam" to "ADAM" we should get two different values when we run the query.
UPDATE Person.Contact
SET FirstName = 'ADAM'
WHERE ContactID = 62
GO
SELECT DISTINCT TOP 10 FirstName
FROM Person.Contact
WHERE FirstName LIKE 'A%'
ORDER BY 1
As you can see we now show both "Adam" and "ADAM" as two different values.
The next thing we are going to do is to create a new table in a case insensitive database and then load all of the data from Person.Contact into this new table.
CREATE TABLE Test.dbo.contact (FirstName nvarchar(50))
GO
INSERT INTO Test.dbo.contact
SELECT FirstName FROM Person.Contact
GO
SELECT DISTINCT TOP 10 FirstName
FROM Test.dbo.contact
WHERE FirstName LIKE 'A%'
ORDER BY 1
GO
When we run the SELECT query you can see that the output combines both "Adam" and "ADAM" since case is ingored.
To get around this we can change the query as follows to force the collation to case sensitive on the FirstName column.
SELECT DISTINCT TOP 10 FirstName COLLATE sql_latin1_general_cp1_cs_as
FROM Test.dbo.contact
WHERE FirstName LIKE 'A%'
ORDER BY 1
When this is run we now have the values of "Adam" and "ADAM".
So depending on how your database is setup you may or may not see the differences. 

To show you another example here is just a quick way of selecting the case sensitive or case insensitive option.
The first query we run is using case sensitive, so all four rows should show up.
select distinct (item) COLLATE sql_latin1_general_cp1_cs_as
FROM (
select 'abcd' item
union all select 'ABCD'
union all select 'defg'
union all select 'deFg') items
All that is different in the next query is the name of the collation. When this query is run using case insensitive, we only get two rows.
select distinct (item) COLLATE sql_latin1_general_cp1_ci_ai
FROM (
select 'abcd' item
union all select 'ABCD'
union all select 'defg'
union all select 'deFg') items
Next Steps
  • You can see how the behavior of the database can impact the output, so next time you are looking for distinct values make sure you understand your database settings or use the COLLATE option
  • Here is another tip that shows you how you can use COLLATE in your WHERE clause Case Sensitive Search on a Case Insensitive SQL Server
  • Special thanks to Andy Novick at Novick Software for this tip idea

Saturday 6 December 2014

Building Your First SSRS Report

Brief Overview

SQL Server 2008 product included one of the services called as “SQL Server Reporting Services 2008 (SSRS)”. SSRS is a full-featured application that provides report design, development, testing, and deployment of reports by the use of Business Intelligence Development Studio (BIDS) developer tool for all its operations.
In this tutorial, we will go through a number of stages so as to understand successful creation and deployment of reports.
Below are the stages of SQL Server Reporting Services we are going to cover in this article.
  • Architecture
  • Installation
  • Role of Business Intelligence Development Studio (BIDS)
  • Report Creation with the Wizard
  • Report Creation with the Report Designer
  • Reports Deployment
  • Security Configuration

Architecture

SSRS block diagram as shown below has been taken from an online book for reference purposes:
  • Reporting Services include an HTTP listener that monitors incoming requests directed to HTTP.SYS on a specific port on the local computer. The host name and port are specified on a URL reservation when you configure the server.
  • Report Manager is an ASP.NET application that provides a browser-based interface for managing Reporting Services.
  • Reporting Services include an Authentication Layer that verifies the identity of the user or application that makes the request. The following authentication types are supported: Windows integrated security, NTLM authentication, Basic authentication, Forms or custom authentication, and Anonymous access. Reporting Services uses Windows integrated security and NTLM authentication by default.
  • The Web Service is also implemented as an ASP.NET application; it provides a programmatic interface to Reporting Services.
  • By default, reporting services creates and uses two SQL Server 2008 databases instances namedReportServer and ReportServerTempDB. The ReportServerTempDB database is used for temporary storage.

Installation

Run the SQL Server 2008 setup.exe program to install SSRS in your machine. Setup will guide you through the various installation and configuration options for the product. Let’s discuss the installation and configuration of SSRS.
Once setup.exe file has been run, you will reach the Feature Selection dialog. Click the checkbox for Reporting Services to perform the installation of SSRS as shown below:
Later in the SQL Server 2008 installation process, you will reach the Reporting Services Configuration dialog as shown below:
Select the first option - Install the native mode default configuration. This will install SSRS and configure it for you automatically. SharePoint integrated mode disables the Report Manager and allows you to store reports in SharePoint document libraries; to run a report, you simply click on it.
The last option is useful where you want to configure the report server manually using the Reporting Services Configuration Manager.
Hurray!!…Half of your job is done. Let’s go in deep to understand more about Reporting services.

Role of Business Intelligence Development Studio

BIDS is included with SQL Server product. When you are running the SQL Server installer, you need to click the Business Intelligence Development Studio checkbox in order to install BIDS. After successfully completing the installation, you are ready to launch BIDS from the Microsoft SQL Server 2008.
On click, BIDS window appears as shown below:
Now, Click File, Add, New Project (ctrl+shift+N) from the top-level menu to add a Report Server project to the solution. Fill in the Add New Project dialog as shown below:
At this point, we have created a Report Server project called “Report Project1” as shown above.

Report Creation with the Wizard

In this section, we will use the Report Wizard to build a report. Let’s go through the following steps:
  • Launch the report wizard
  • Create a shared data source
  • Design a query
  • Select a report type
  • Design a table
  • Choose the table layout
  • Complete the wizard

Launch the Report Wizard

The BIDS Solution Explorer shows our Report Server project along with Shared Data Sources and Reports components as shown below:
Right click on the Reports node and select Add New Report to launch the Report Wizard as shown below:
(Note: The checkbox "Don't show this page again" if you don’t want to see this dialog box every time you want to design a new report using the wizard.)
Click the Next button to create a data source for your report which we will explain in the next section.

Create a Shared data Source

The Data Source contains the information necessary to retrieve the data we pull in our report. Reporting Services can access data in relational databases, OLAP databases, and any other data source for which like an ODBC or OLE DB driver, etc.
When we create a Data Source, we can specify it as shared which means it can be used by any report in the same project. If a Data Source isn't shared, it means that its definition is stored inside the report and it cannot be shared with another report.
After launching the Report Wizard, you will be presented with the Select the Data Source dialog as shown below:
Our project does not have any Shared Data Sources yet, so we do not have the option of selecting an existing Shared Data Source for our new report. Instead, we will have to define a Data Source by supplying the below parameters:
  • Name - Name for the Data Source. Do not put any spaces in the name; if you do, you will get an error message when you complete the wizard and your Data Source will not be created.
  • Type - Select from the available options in the dropdown list; for demonstration purposes, ReportServerdatabase that we are using.
  • Connection String - Enter the connection string for your Data Source; usually you will want to click the Edit button to enter the details and have the connection string created for you.
  • Edit button - Rather than entering the connection string, click this button to display the Connection Properties dialog where you can enter some information and have the connection string created for you.
  • Credentials button - Click this button to display the specify Data Source Credentials dialog where you can specify the credentials to use when connecting to your Data Source.
  • Make this a shared data source checkbox - click this checkbox to create a Shared Data Source; any report in the same project can the use this Data Source.
Click the Edit button to display the Connection Properties dialog; fill in your Server name and select theReportServer database as shown below:
The Server name is the one where your SQL Server database is deployed. If you are running a named instance of SQL Server, you will need to specify the Server name as SERVERNAME\INSTANCENAME. If you are running SQL Server locally, you can specify localhost in place of SERVERNAME. You should click the Test Connection button to verify that you are able to connect to the database, then click OK to close the dialog.
Click the Credentials button to display the Data Source Credentials dialog as shown below:
The default selection is Use Windows Authentication (Integrated Security). This means that Reporting Services will connect to the Data Source using the Windows credentials of the person running the report. After completing the above steps, the Select the Data Source dialog will look as shown below:
Click Next to move on to the Design the Query dialog which we will discuss in the next section.

Design the Query

The Design Query step of the Report Wizard allows us to specify what data we want to retrieve from our Data. In this section, we will walk through the details of defining a query to retrieve the data to get on our report.
The Design the Query step in the Report Wizard will display the dialog as shown below:
You can click the Query Builder button to graphically build your query or you can type your query directly into the Query window. The following is an example of a query that you could type in:
SELECT  E.EMP_NAME,
   E.DESIGANTION,
   E.HIRE_DATE,
   E.SALARY,
   D.DEPT_NAME,
   D.LOCATION
FROM
   EMPLOYEE E
INNER JOIN
   DEPARTMENT D
   ON
   E.DEPT_NO = D.DEPT_NO
ORDER BY
     E.EMP_NAME ASC
Click Next to move on to the Select the Report Type dialog which we will discuss in the next section.

Select the Report Type

The Select Report Type step of the Report Wizard allows us to choose from a Tabular or Matrix type of report.
The Select Report Type step in the Report Wizard will display the dialog as shown below:
The tabular report type is the traditional report with page headings, column headings, subtotals and totals running down the page.
The matrix report type is one where we define the fields that go on the columns and rows. We'll create a tabular report as this will provide a simple and familiar picture.
Click Next to move on to the Design the Table dialog which we will discuss in the next section.

Design the Table

The Design Table step of the Report Wizard allows us to layout the available fields on our report choosing between Page, Group, and Details.
The Design Table step in the Report Wizard will display the dialog as shown below:
The Available fields list is populated based on the query that you used in the previous step in the Report Wizard. Click on a field, then click on the appropriate button where you want to put that field. Fill in the dialog as shown below:
The following is a description of the buttons in the above dialog:
  • Page - Put a field in this list when you want to begin a new page when the value of the field changes
  • Group - Group by the fields in this list
  • Details - The fields in this list appear in each row of the report
Click the Next button to move on to the Choose the Table Layout dialog which we will discuss in the next section.

Choose the Table Layout

The Choose Table Layout step of the Report Wizard allows us to choose a stepped or blocked layout and whether to include subtotals and enable drilldown.
The Choose Table Layout step in the Report Wizard will display the dialog as shown below:
The default of Stepped displays the groupings as shown above. Block moves things in a little closer which saves space, but it disables drilldown. Include Subtotals will provide fields totals based on groupings as per user wish. Enable drilldown will initially hide details and allow expanding with a click on the plus icon. Fill in the dialog as shown below:
Click the Next button to move on to the Chose the Table Style dialog which we will discuss in the next section.

Choose the Table Style

The Choose Table Style step of the Report Wizard allows us to choose from a number of different styles.
The Choose Table Style step in the Report Wizard will display the dialog as shown below:
Choose a style from the list of available options, then click the Next button to move on to the Completing the Wizard dialog which we will discuss in the next section.

Complete the Wizard

The Completing the Wizard step in the Report Wizard will display the dialog as shown below:
You should provide a descriptive name for your report in the Report Name textbox. You can click the Preview report check box if you would like to see what your report will look like. Click the Finish button to complete wizard. You will now see your report in the Solution Explorer as shown below:
The report will also be displayed in the Report Designer. Click on the Preview tab to render your report. A portion of the report is shown below:
We're going to make a couple of changes to the report. Click on the Design tab; you will see the following:
We're going to put spaces between the words in the heading, make the columns a little wider, and add subtotal and total for DEPT_NAME and LOCATION.
Here are the steps explained below:
  • Click in the report heading between and add spaces as desired for all report columns.
  • Add subtotal for location-wise and department-wise.
After making these changes, the report design should look like this:
Click on the Preview tab to display the report:
You can click on the + icon to the left of the Location Category Names to drilldown to Location details as shown below:
This completes the tutorial section on the Report Wizard.

Reports Deployment

When we have completed a report, we need to deploy it so that other users can run it. In this section, we will show how to deploy reports using the following methods:
  • Business Intelligence Development Studio (BIDS)
  • Report Manager
In SQL Server 2005 Reporting Services, you could also use SQL Server Management Studio to deploy reports. This functionality has been removed in SQL Server 2008.
You can also deploy reports to a SharePoint when you configure Reporting Services in SharePoint Integration mode. We are not covering SharePoint Integration in this tutorial.
Now let's continue on to the next section to see how to deploy reports from BIDS.
Business Intelligence Development Studio (BIDS) provides the capability to deploy reports. In order to deploy our reports and data sources using BIDS, we need to specify some project settings. Right click on the project name in the Solution Explorer, then select Properties from the popup menu; you will see the dialog as shown below:
The following is a description of the deployment settings:
  • OverwriteDataSources - Specify True or False; when you deploy using BIDS you probably want to choose False so that if the Data Source already exists on the deployment target, it does not get overwritten. The reason is that the connection strings in your development server are probably different than your target deployment server.
  • TargetDataSourceFolder - The name of the folder where you want to deploy you Data Sources; the default value is Data Sources.
  • TargetReportFolder - The name of the folder where you want to deploy your reports. The default value is the name of your project.
  • TargetServerURL - The URL of your deployment report server; use the formathttp://SERVERNAME/reportserver where SERVERNAME is the name of the server where you installed Reporting Services.
Now, you can deploy all data sources and reports in the project by right clicking the project in the Solution Explorer and selecting Deploy from the popup menu. You can deploy specific data sources and/or reports by selecting them in the Solution Explorer (use Ctrl + Click to select multiple), right clicking on a selected item and choosing Deploy from the popup menu.
The success or failure of your deployment will be reported in the Output window as shown below:
After successful deployment of our sample project, you will see the following on the Report Manager home page:

Report Manager

Report Manager is the web-based application included with Reporting Services that handles all aspects of managing reports. In this section, we will walk through report deployment using the Report Manager.
To begin, navigate to the Report Manager home by opening your browser and typing in the URL; e.g.http://SERVERNAME/reports, where SERVERNAME is the server where the Report manager is deployed.
To deploy using the Report Manager, we would create folders for our data sources and reports as shown above by clicking the New Folder button. Navigate to the new folder by clicking on it, and then click the Upload File button to upload either a data source or a report.
When you click the New Folder button, the following dialog will be displayed:
Fill in the Name textbox; e.g. SSRS Deployed Report and click OK. The Report Manager now displays the new folder as shown below:
Click on the SSRS Deployed Report to navigate to the folder. We are going to create a new data source and upload a report.
When you deploy your data sources, the settings will be different than what you have in your development environment. Click the New Data Source button to create and configure your data source as shown below:
The following are the main points about creating a data source:
  • You have to enter the connection string;
  • The Connect using option selected is Windows integrated security
  • The Credential stored securely in the report server option along with checking Use as Windows credentials.
After clicking OK to finish creating your data source, click the Upload File button to deploy a report. Fill in the dialog by navigating to the report that you want to deploy and picked the file Employee_Details.rdl file.
After clicking OK to deploy the report, you can click on the report in the Report Manager to display report output and hence, you have uploaded report file successfully.
That’s it!!! This finishes your first report using SQL Server Reporting Services.

Using TRY...CATCH in Transact-SQL

How it works

As I have mentioned this TAC block is very similar to what be use in other.net languages. The idea is fairly simple
When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed.
Begin Try 
 The_Query_for_which_we_need_to_do_the_ Error_Handling 
End Try 
Begin Catch 
   
  If there is some error in the query within the Try block, this flow 
  will be passed to this Catch block. 
  
End catch 
If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.

Do TRY/CATCH blocks work for all errors?

No, please do take care of the following points
  • A TRY block must be followed immediately by a CATCH block.
  • TRY…CATCH constructs can be nested, which means that TRY…CATCH constructs can be placed inside other TRY and CATCH blocks. When an error occurs within a nested TRY block, program control is transferred to the CATCH block associated with the nested TRY block.
BEGIN TRY           
      Print ' I am level 1 '
          BEGIN TRY
            Print ' I am level 2 '
            SELECT 1/0;    
          END TRY
          BEGIN CATCH    
   SELECT 
   ERROR_NUMBER() AS ErrorNumber,
   ERROR_SEVERITY() AS ErrorSeverity,
   ERROR_STATE() as ErrorState,
   ERROR_PROCEDURE() as ErrorProcedure,
   ERROR_LINE() as ErrorLine,
   ERROR_MESSAGE() as ErrorMessage;   
    Print ' I am going out level 2   ' 
         END CATCH
    Print ' I am level 1 again '    
END TRY
BEGIN CATCH
    -- Execute the error retrieval routine.
        SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;    
END CATCH

Output

 I am level 1 
 I am level 2 
----------- 
(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134        16            1          NULL               5     Divide by zero 
                                                            error encountered. 
(1 row(s) affected) 
 I am going out level 2   
 I am level 1 again 

In the code above the error is thrown from the Level 2 TAC block 
  • To handle an error that occurs within a given CATCH block, write a TRY…...CATCH block within the specified CATCH block.
  • Errors with a severity of 20 or higher that cause the Database Engine to terminate the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not terminated.
  • Errors with a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.
From the above two statement it is clear that
Try & Catch block will work for
Errors with a severity of 10 < TRY & Block <= Errors with a severity of 20

Error Functions

TRY…CATCH uses error functions to capture error information.
ERROR_NUMBER() returns the error number.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
Error information is retrieved using these functions from anywhere within the scope of the CATCH block of a TRY…CATCH construct. The error functions will return NULL if called outside the scope of a CATCH block. Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed within the CATCH block. By doing this, it will not be necessary to type error handling code in every CATCH block.

Will the TRY / CATCH blocks catch Compile errors, such as syntax errors that prevent a batch from executing?

No, the TAC block will not catch the compile errors, if it is not called in the from of dynamic query or in some SP
In the below code the table does not exist, so we will get the error while we want to complie the code.The TAC block does not play any role here
BEGIN TRY
    -- This PRINT statement will run since the error
    -- occurs at the SELECT statement.
    PRINT N'Starting execution'
   
   SELECT * FROM NonExistentTable
    -- This SELECT statement will generate an object name
    -- resolution error since the table does not exist. 
      
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO 
OutPut : 
Starting execution
Msg 208, Level 16, State 1, Line 6
Invalid object name 'NonExistentTable'.
I will try to put the above logic using a dynamic SQL. On doing so the code will compile, but will through a error, which will be caught by the TAC block

Dynamic query

BEGIN TRY
    -- This PRINT statement will run since the error
    -- occurs at the SELECT statement.
    PRINT N'Starting execution';
    DECLARE @SQL NVARCHAR(2000) 
    SET @SQL = 'SELECT * FROM NonExistentTable;'
    -- This SELECT statement will generate an object name
    -- resolution error since the table does not exist.
   EXEC sp_executesql  @SQL 
      
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO 

Output : 
Starting execution
ErrorNumber ErrorMessage
----------- ------
208         Invalid object name 'NonExistentTable'. 
(1 row(s) affected)

Dealing with Transactions

In the TRY statement it may happen that we open a Transaction, but for some reason, the statement went to the respective catch block, then how is the open Transaction is going to behave.
In this state, however, the locks acquired by the transaction are maintained, and the connection is also kept open. The transaction's work is not reversed until a ROLLBACK statement is issued.
The code in a CATCH block should test for the state of a transaction by using the XACT_STATE function. XACT_STATE returns a -1 if the session has an uncommittable transaction. The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1. The following code example generates an error from a DDL statement and uses XACT_STATE to test the state of a transaction in order to take the most appropriate action.
CREATE TABLE TEMP ( A INT ) 
BEGIN TRY 
       -- BEGIN   Tran   
         ALTER TABLE TEMP
            DROP COLUMN author                 
         COMMIT TRAN 
END TRY
BEGIN CATCH
    -- Execute the error retrieval routine.
        SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;   
        PRINT ' *****Value of XACT_STATE ****'+CONVERT(VARCHAR,XACT_STATE()) 
END CATCH
GO 
Output 
ErrorNumber ErrorSeverity ErrorState  ErrorProcedure  ErrorLine   ErrorMessage
----------- ------------- ----------- --------------------------------------
3930        16            1           NULL    3       The current transaction
                                                      cannot be committed and 
                                                      cannot support
                                                      operations that write
                                                      to the log file. Roll 
                                                      back the transaction.
(1 row(s) affected) 
 *****Value of XACT_STATE ****-1

PIVOT and UNPIVOT in Sql Server

PIVOT

PIVOT is one of the New relational operator introduced in Sql Server 2005. It provides an easy mechanism in Sql Server to transform rows into columns.
To understand PIVOT with extensive list of examples, let us first create a Temporary Table #CourseSales with sample records as depicted in the below image by using the following script:
Table to be Pivoted in Sql
--Create Temporary Table #CourseSales
CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning  MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('.NET',2012,5000)
INSERT INTO #CourseSales VALUES('.NET',2013,48000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO

Example 1:

In this example the #CourseSales Table data is Pivoted so that the Course becomes the column headings.
Pivot Example 1 In Sql Server
Below script pivots the #CourseSales Table data so that the Course columns distinct values are transformed as Columns in the result set as depicted in the above image.
SELECT *
FROM #CourseSales
PIVOT(SUM(Earning)
      FOR Course IN ([.NET], Java)) AS PVTTable

Example 2:

In this example the #CourseSales Table data is Pivoted so that the Year becomes the column headings.
Pivot Example 2 In Sql Server
Below script pivots the #CourseSales Table data so that the Year columns distinct values are transformed as Columns in the result set as depicted in the above image.
SELECT *
FROM #CourseSales
PIVOT(SUM(Earning)
      FOR Year IN ([2012],[2013])) AS PVTTable

Example 3:

Transforming Sales Table data to a Quarterly aggregated sales data with Quarters as the Columns in the result set.
Pivot Example 3 In Sql Server
Let us create a Temporary Sales table and insert 1000 Sample Sales Records With Random past 0-1000 days as the sales date by using the below script.
--Create Temporary Sales Table
CREATE TABLE #Sales
(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With
--Random past 0-1000 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (1000+1)*RAND()),GETDATE()))
GO 1000
Now we can write a query like below which Pivots the Sales Table Data such that the quarters are the columns in the final result set as depicted in the above image.
SELECT Year, QPivot.[1] As Q1, QPivot.[2] As Q2,
 QPivot.[3] As Q3, QPivot.[4] As Q4
FROM (SELECT YEAR(SalesDate) [Year],
     DATEPART(QUARTER, SalesDate) [Quarter],
         COUNT(1) [Sales Count]
      FROM #Sales
      GROUP BY YEAR(SalesDate),
         DATEPART(QUARTER,SalesDate)) AS QuarterlyData
PIVOT( SUM([Sales Count])  
        FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot

Example 4:

Transforming Sales Table data to a Monthly aggregated sales data with Months as the Columns in the result set.
Monthly Data Using Pivot In Sql Server 2005
We can write a PIVOT query like below to get the Monthly Data as depicted in the above image.
SELECT *
FROM (SELECT YEAR(SalesDate) [Year],
       DATENAME(MONTH, SalesDate) [Month],
       COUNT(1) [Sales Count]
      FROM #Sales
      GROUP BY YEAR(SalesDate),
      DATENAME(MONTH, SalesDate)) AS MontlySalesData
PIVOT( SUM([Sales Count])  
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot

UNPIVOT

UNPIVOT is the reversal of the PIVOT operation. It basically provides a mechanism for transforming columns into rows.
UNPIVOT Example in Sql Server
From the above image it is clear that UNPIVOT is the reversal of the PIVOT operation. But it is not the exact reversal of PIVOT operation as PIVOT operation generates the aggregated result, so UNPIVOT will not be able to split the aggregated result back to the original rows as they were present prior to the PIVOT operation.
As depicted in the above image there were 5 rows in the #CourseSales Table Prior to PIVOT, but a PIVOT and it’s reversal UNPIVOT resulted in 4 rows only. The reason for this is for .NET Course in Year 2012 there were two records one with earning 10K and another with earning 5K, the PIVOT and it’s reversal UNPIVOT result last lost this split information and instead of two rows it has generated one row for the .NET course in Year 2012 with Earning as sum of 10K and 5K i.e. 15K.
We can use the below script to simulate the PIVOT and UNPIVOT operation as depicted in the above image on the #CourseSales Temporary Table created in the beginning PIVOT section of this article.
--PIVOT the #CourseSales table data on the Course column
SELECT *
INTO #CourseSalesPivotResult
FROM #CourseSales
PIVOT(SUM(Earning)
      FOR Course IN ([.NET], Java)) AS PVTTable
GO
--UNPIVOT the #CourseSalesPivotResult table data
--on the Course column   
SELECT Course, Year, Earning
FROM #CourseSalesPivotResult
UNPIVOT(Earning
      FOR Course IN ([.NET], Java)) AS UNPVTTable