Creating Excel Pivot Table using SQL Database

Creating Excel Pivot Table using SQL Database-Table

A pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations.

Now in order to Create Pivot Table in Excel using SQL Database in SQL 

First create a table in SQL with Below Script

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee1] ON
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'Amit', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'Sumit', 6000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'Raj', 8000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'vijay', 9000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'suresh', 10000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, NULL, 5000)
SET IDENTITY_INSERT [dbo].[Employee1] OFF

Now open Excel 

---First we will create a Dataconnection to SQL Table for this below are the steps

Go to Data Tab 
Connection
ADD
Browse for More
New Source
SQL Server
-Connection Name
-Select your Database Name
-Select Table Name-Employee 1--above table which we have created
-click ok

Now in Order to create Pivot table

Go to Insert Tab
-Pivot Table
-It will ask for connection file
select the Name of connection file which we created above
click ok 

In excel you will see the the column Name in Right hand Side.

Drop EmpName and EmpID to Row Labels and Salary field to Values. It will Display Total Salary Based on  EmpID and EmpName.Its same as Group by in SQL displaying Sub total also.

IF you don't want subtotal of each EmployeeName Select your Pivot Table

 Go to Pivot Table tools-Design

-SubTotals

-Do Not show Sub Totals

You will have your Pivot table displaying Employee Name ,ID and Sum of Salary.