RSS

Convert Result Set to pivot table

27 May

Introduction

Sometime you need to change the table view to see it as MATRIX or by other word pivot table let’s start to discuss this example by see bellow table

Id

FullName

QoutaAmount

Year

arter

1

Ahmed

50000

2008

1

2

Ahmed

70000

2008

2

3

Ahmed

90000

2008

3

4

Ahmed

10000

2008

4

5

Ayman

50000

2008

1

6

Ayman

70000

2008

2

7

Ayman

90000

2008

3

8

Ayman

10000

2008

4

9

Mohamed

50000

2008

1

10

Mohamed

70000

2008

2

the above table is regular table you need to change its view to other view like bellow

FullName

2008

2009

Ahmed

220000

220000

Ayman

220000

220000

so how we can made this by SQL Server 2005 and SQL Server 2008

you must first ask yourself 4 Questions

  1. What is the pivot Column which will convert it to Column Header? Ans : Year
  2. What is the non-pivot Column? Ans :FullName
  3. What is the Aggregate Column which will be cross value between pivot column and Non-pivot column?                 Ans : QoutaAmount
  4. What is the aggregation Type? Ans : Sum

Syntax

Select <Non-Pivot Column>,[Pivot Column Value1],[Pivot Column Value2]…

From (Select [Non-Pivot Column],[Pivot Column], [Aggregate Value] From SourceTable) as tb

Pivot( AggregateFunc(AggrgateValue) for [Pivot Column] in ([Pivot Column Value1],[Pivot Column Value2]..))as pvt

Example

 

USE [AdventureWorks]

GO

 

/****** Object:  Table [dbo].[Qouta]    Script Date: 05/27/2009 20:03:55 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[Qouta](

      [Id] [int] IDENTITY(1,1) NOT NULL,

      [FullName] [nvarchar](50) NULL,

      [QoutaAmount] [float] NULL,

      [Year] [int] NULL,

      [Quarter] [nvarchar](50) NULL

)

 

GO

SET IDENTITY_INSERT [Qouta] ON

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (1, N’Ahmed’, 50000, 2008, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (2, N’Ahmed’, 70000, 2008, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (3, N’Ahmed’, 90000, 2008, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (4, N’Ahmed’, 10000, 2008, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (5, N’Ayman’, 50000, 2008, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (6, N’Ayman’, 70000, 2008, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (7, N’Ayman’, 90000, 2008, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (8, N’Ayman’, 10000, 2008, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (9, N’Mohamed’, 50000, 2008, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (10, N’Mohamed’, 70000, 2008, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (11, N’Mohamed’, 90000, 2008, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (12, N’Mohamed’, 10000, 2008, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (13, N’Ahmed’, 50000, 2009, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (14, N’Ahmed’, 70000, 2009, N’2′)

INSERT [Qouta] ([Id],
[FullName], [QoutaAmount], [Year], [Quarter]) VALUES (15, N’Ahmed’, 90000, 2009, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (16, N’Ahmed’, 10000, 2009, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (17, N’Ayman’, 50000, 2009, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (18, N’Ayman’, 70000, 2009, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (19, N’Ayman’, 90000, 2009, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (20, N’Ayman’, 10000, 2009, N’4′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (21, N’Mohamed’, 50000, 2009, N’1′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (22, N’Mohamed’, 70000, 2009, N’2′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (23, N’Mohamed’, 90000, 2009, N’3′)

INSERT [Qouta] ([Id], [FullName], [QoutaAmount], [Year], [Quarter]) VALUES (24, N’Mohamed’, 10000, 2009, N’4′)

SET IDENTITY_INSERT [Qouta] OFF

 

 

go

 

 

 

Select FullName,[2008],[2009]

From

(Select Fullname,QoutaAmount,[YEAR] from Qouta)tb

pivot(sum(QoutaAmount) for[YEAR] in ([2008],[2009]) )pvt

 

 

Advertisements
 
Leave a comment

Posted by on May 27, 2009 in SQLServer, T-SQL, Warehouse

 

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: