How easily you can unpivot the pivot data in SQL Server?

Pivot table indiandotnet

I know when we talk about the pivoting & unpivoting the data then most of the time we are making our faces and we feel it would be a tough task. Trust me after reading this post you feel unpivot is super easy.

Before jumping directly into unpivot want to share pivot link to take a glimpse if you are not aware of it.

Pivot in SQL Server pivot 

Now, let us assume that we have following table of employee with id,name,weekid and Dayname columns.

DECLARE @tblEmployeeDayWiseAttendace AS TABLE (Id INT IDENTITY(1,1),
EmployeeName VARCHAR(100),
WeekId SMALLINT,
Monday TINYINT,
Tuesday TINYINT,
Wednesday TINYINT,
Thursday TINYINT,
Friday TINYINT,
Saturday TINYINT,
Sunday TINYINT)

Now let’s insert few rows into it

INSERT INTO @tblEmployeeDayWiseAttendace (EmployeeName,WeekId,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
VALUES(‘Sandeep’,1,8,8,8,8,8,0,0),
(‘Sunil’,1,8,8,8,8,8,0,0),
(‘Shreya’,1,7,6,8,8,8,0,0),
(‘Shweta’,1,8,8,8,0,5,0,0),
(‘Priya’,1,8,8,8,8,8,8,0),
(‘Rashmi’,1,9,8,9,8,8,4,0),
(‘Bhushan’,1,4,8,5,8,2,0,0)

If you run SELECT * FROM @tblEmployeeDayWiseAttendace then you will get following data as shown in below snap.

Pivot table indiandotnet

Now, the challenge is to Convert Columns Monday,Tuesday,Wednesday and other day columns to row corresponding to employee and show their value.

To make it very easy you have to write below CROSS APPLY query

SELECT tmp.Id, tmp.EmployeeName,tmp.WeekId,tmp2.weekdayname,tmp2.weekValue
FROM @tblEmployeeDayWiseAttendace tmp
CROSS APPLY(values(‘Monday’,tmp.Monday),
(‘Tuesday’,tmp.Tuesday),
(‘Wednesday’,tmp.Wednesday),
(‘Thursday’,tmp.Thursday),
(‘Friday’,tmp.Friday),
(‘Saturday’,tmp.Saturday),
(‘Sunday’,tmp.Sunday))tmp2(WeekDayname,weekValue)

Once you run this query you will get the output which you require.

Now, tell me do you still afraid from unpivot.

Share your thoughts & inputs in comment.

Cheers!

RJ

Why Do People Think Learn Step by Step PIVOT Transformation is a Good Idea ? TIP #125

In the series of step by step SSIS learning, this is another interesting article. In this article we will try to implement PIVOT transformation in few easy steps.

Let’s understand the PIVOT by below example. Suppose, you have a salary data of employee as shown below.

Employee Name Salary($) Year
Sandeep 110000 2016
Sunil 120000 2016
Shreya 130000 2015
Virendra 140000 2015
Sandeep 120000 2014
Sunil 130000 2014

Now, if our requirement is something like below table then we would require PIVOT functionality.

Employee Name 2016 2015 2014
Sandeep 110000 0 120000
Sunil 120000 0 130000
Shreya 0 130000 0
Virendra 0 140000 0

In general, we say when we want to convert Rows data to Columns then this is called Pivoting.

Now, lets create a sample of PIVOT transformation with few basic steps. In this example, we are again using AdventureWorks database. EmployeePayHistory, Employee, People tables are base of our example. We wrote query which fetch data similar as shown above.

4

Let’s move step by step

Step 1: Add a dtsx package and drag drop data flow task as show in below figure.

1

Step 2:- Now, double click on Data flow task and drag drop data source and select SQL Data source and configure the SQL statement as shown below.

2

3

Step 3:- Now, We have to drag drop PIVOT transformation control as shown below.

5

Step 4:- Now, we have to configure the  PIVOT control. When you choose edit option then you will get the following screen.

Here, we have to choose Pivot Key, Pivot Value and Set Key.  In the Pivot Value we use the value which we have to show in the columns.

 

15

If you see pivoted output columns highlighted in yellow in above image is the year wise columns. if you are sure for the columns which you want as a pivot columns then you can add the name of the columns in place of [value1],[value2] etc. and click on Generate Columns Now button. (In current case we have limited years so we have added [1997] to [2004] values in this textbox and clicked on Generated Columns Now button due to which we got yellow required Pivot output columns as highlighted yellow.

Step 5:- Our configuration is not yet complete to configure this we have to click on advance setting and select Input and output properties.

Here, if you want to add any extra column as an output column which exist in Input columns  then you have to select Pivot Default Output and click on add column. Now, the main important point if we want to map it with existing input source column then in such case we have to add LineageId of that particular column.(AS highlighted in below images)

7

8

Step 6:- The another most important point is PivotUsage which is highlighted below. If we want to add columns which are not affecting the pivot then we have to update the value of PivotUsage to 0.

9

Step 7:- Once the Pivot is configure the next step is to provide the output of pivot to a destination. As a destination we are using Excel file as usual Smile.

So, drag drop excel destination source and configure it as shown in below figure .I hope you will not face any issue in this. If you are new and facing any difficulties then please visit previous articles of Zero to Hero in SSIS Series

10

11

Step 8 :- Once the configuration is done we will see below screen.

12

Step 9:- Now, once everything is completed then we run the package and we will get following screen.

13

Now to cross check we open the excel and cross check the data. When we open the excel, we found desire result.

14 

So, We have achieved what we want. We did our first Pivot Transformation example.

Please, feel to drop your comment.

Enjoy !!!

RJ!!!

Pivot Viewer Example ” Cricket World Cup 2011″

Hello friends,
We have left the last topic with definition of Pivot Viewer now in this post we are going to make a sample which use pivot viewer. Our sample is “Cricket World Cup 2011” so here we go
Step 1:- First checks on your machine following things are installed if not then please installed them
a) Silverlight 4 (http://silverlight.codeplex.com/ )
b) Pivot collection tool (http://www.silverlight.net/learn/pivotviewer/collection-tools/)
Step 2:- Once you have installed above tools then create a pivot collection first. The pivot collection can also be generate using code also (at run time) but here I am using excel pivot collection generator.
Step 3:- For generating collection just open excel book go to pivot collection menu when you click pivot collection just below the menu new pivot collection tools will be visible. Then just create new collection button as shown in above image.

Excel Pivot Collection

Step 4:- When you click the new collection button you will get new excel object as shown in below fig

Pivot Collection Template

Step 5:- Now add the data according to your need as I added following columns
a) Team
b) Name
c) Bating Style
d) Bowling style
e) Is Captain
f) 100’s
g) 50’s
h) Runs
i) Average
j) URL
k) Description
l) Image
Step 6: Just fill the data as per the filtration column as shown in below fig

Cricket World Cup 2011 Player List
Step 7: Now once your data is fill then just click on publish collection button on toolbar
It will generate a “CXML” file which is collection Xml file with   deep zoom images collection.

Step8:- Now your collection is ready so next thing is how to use it. So first create a VS2010 Silverlight web application project.


Step 9: Copy CXML & and images folder to your web project first
Step 10: Once you copied the CXML and Images then open your Silverlight project and drag drop Pivot control or if it is not exist in your tool manually add for that just add reference as shown below.

xmlns:pivot=”clr-namespace:System.Windows.Pivot;assembly=System.Windows.Pivot”

Step 11:- Now in the code behind just assign the collection URL to pivot control. (The URL nothing but the CXML URL which you already copied in your web project in step 9) as shown below.
this.pivot.LoadCollection(“http://localhost:54210/CricketWorldCup2011.cxml“, “”);

Step 12: Now run the project you will get following screen. On left side you will get filter criteria when you change that your data will also change in center.

Cricket World cup 2011 Team pivot
Cricket World Cup 2011 Pivot

Hope the steps will be helpful and you can download code from.

Excel Data Cricket World Cup Team 2011 Data

Solution of Pivot Viewer Download Solution
Thanks & Esteemed Regards
Rajat Jaiswal

What is Pivot Viewer in Silverlight 4?

Hi Folks,
Sorry for such a late post. I know you were waiting for the new post from last 2 week. So here I am with a new interesting area which will help in data visualization. “Pivot Viewer” control is great tool supported by Silverlight 4. I know that I am bit late to explore such a great control.
I really want to thank Microsoft Pivot team for this they did such a great job.
Again our 3 basic questions WWH means,
what is Pivot Viewer control?
Why Pivot Viewer control?
And lasts but not the least How we can use & utilize it?
So, we first start with pivot viewer. “Pivot Viewer” is new control supported with Silverlight 4.
It is a representational control you can use this control where you want a BI functionality. Or we can say that it provide a unique way to represent data to analyse. You can filter data on the basis of various filters which you define and the transition from one filter to another filter is so smooth you worth to watch it. You can change view from normal grid view to column view.
Now if you are aware of BI (Business Intelligence) tools then I am just saying here it is one of the BI tools which help end user to get the result by applying multiple filters. You can define facts & fact category (facts are actual data & fact categories are the fields on which you can apply filter to get desire result).
Now I know you are most interested in where & how can we use?  So I am taking very interesting topic which is “Cricket World Cup 2011”.
Pivot viewer silverlight 4 with Cricket World cup 2011
Cricket World Cup 2011 pivot viewer

I will explain whole topic with example in next post.

Thanks
Rajat Jaiswal

Pivot in SQL SERVER

Hello friends,

Today I will explain you how to use pivot table. It’s very useful.

For example just take  a look on below   statements

 

DECLARE @tblTest AS TABLE (strName        VARCHAR(50),

                           fltTotalAmount DECIMAL(10,3),

                           intMonth       TINYINT,

                           intYear        INT)

 

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘10000’,1,2006)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘15000’,2,2006)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘16000’,3,2006)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘10700’,1,2007)

INSERT INTO @tblTest (strName, fltTotalAmount, intMonth, intYear)

VALUES (‘RAJAT’, ‘16000’,2,2007)

 

Now suppose if you want sql result in following format

Name

Year  

Jan         

Feb       

march

RAJAT

2006

10000.000

15000.000

16000.000

RAJAT

2007

10700.000

16000.000

NULL

  Then what you have to do?

You have to do following thing just take a look below statements.

 

SELECT strName,

       intYear,

       [1] As Jan,

       [2] AS Feb,

       [3] As mar

FROM ( SELECT strName,

       intYear,

       fltTotalAmount,

       intMonth

       FROM @tblTest )p

PIVOT (SUM (fltTotalAmount) FOR intMonth IN ([1],[2],[3]))Q

 

Now when you execute it it will give you desire result.

 

I hope you got the idea how to use pivot table.

If you have any kind of problem or confusion in the above statement feel free to drop a comment.

Thanks

Regards

Rajat Jaiswal