Sky Runner

Most of us have been using Data Transformation Services (DTS) in the previous versions of SQL Server in order to manipulate or move data. With the introduction of SQL Server 2005, Microsoft has introduced a completely re-written version of DTS with a lot more new features which is now known as Integration Services. This new ETL platform is nothing but a set of utilities, applications, designers, components, and services all wrapped up into one powerful software application suite. The most attracting feature of SSIS is that the data movement and transformation is separate from the package control flow and management. There are two different engines that handle these tasks. Integration Services Data Flow engine takes care of the data movement and transformation whereas the Integration Services Run-time engine manages the package control flow. The motivation behind this article is that the SSIS API is not well documented in MSDN though you get an overall idea of what is going on. This article assumes that the reader is aware of the fundamental components of an SSIS Package.

What is a Pivot Table ?

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values. Pivot tables are easily created by office users using Microsoft Excel or MS Access.
Since pivot table enables report builders and BI (Business Intelligence) specialists empower their presentation of reports and increase the visibility and unserstandability of mined data, pivot tables are common and preferred widely. Pivot tables display data in tabular form. The pivot table formatting is not different than a tabular report formatting.

But the table columns are formed by the report data itself. I mean as a pivot table example, your report creator can build a report with years and months in the left side of the table, the main product lines are displayed as columns, and total sales of each product line in the related year and month is displayed in the cell content.

Actually you can easily answer what is pivot table question, if you have build OLAP reports or if you are familiar with OLAP reporting. Pivot Table in sql grants the ability to display data in custom aggregations just like OLAP reports in SQL Server. Simply pivot tables can be thought of transforming a table with its data into another table format. Just as building a sales report in months and product lines from Sales Orders table.

In this post, I have given the difference between the use of Joins and the use of Union keyword in SQL.

I have a table named Table1 with Severity, Response SLA and Resolution SLA as its columns. Now I need to take the number of issues for both Response SLA and Resolution SLA based on Severity.

1. Using Inner join:

SELECT A.[Severity],A.[ResponseSLA],A.[Count],B.[ResolutionSLA],B.[Count] FROM
(
SELECT [Severity],[ResponseSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
GROUP BY [Severity],[ResponseSLA]
) A
INNER JOIN
(
SELECT [Severity],[ResolutionSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
GROUP BY [Severity],[ResolutionSLA]
) B
ON A.[Severity] = B.[Severity]
Order By A.[Severity]

The above query fetches the following result:

Severity	ResponseSLA	Count	ResolutionSLA	Count
Sev2	Not Met SLA	1	Not Met SLA	1
Sev3	Met SLA	29	Met SLA	31
Sev3	Not Met SLA	2	Met SLA	31
Sev4	Met SLA	597	Met SLA	613
Sev4	Not Met SLA	16	Met SLA	613

Note: You can replace "SELECT A.[Severity],A.[ResponseSLA],A.[Count],B.[ResolutionSLA],B.[Count] FROM" With "SELECT * FROM" and fetch all the columns.

2. Using Union:

Consider you need to calculate the total count. Then you should query as below.

SELECT * FROM
(
SELECT [Severity],[ResponseSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
GROUP BY [Severity],[ResponseSLA]
) A
UNION
(
SELECT 'Total' [Severity],'' [ResponseSLA], Count(1) As [Count] FROM Table1
WHERE GroupID = 4 AND [Month] = 'Mar - 2010'
)

The above query fetches the following result:

Severity	ResponseSLA	Count
Sev2	Not Met SLA	1
Sev3	Met SLA	29
Sev3	Not Met SLA	2
Sev4	Met SLA	597
Sev4	Not Met SLA	16
Total		645

Note: In UNION, the number of columns and the column names should match. In this case, the columns of both A & B should match each other.

Hope you understood the difference. Joins always add the columns to the left and the union statements add the columns to the bottom of the result grid.