Man Made Code




















Statistics

Total Posts: 34
This Year: 0
This Month: 0
This Week: 0
Comments: 174


RSS 2.0   SocialTwist Tell-a-Friend


Admin

Sign In

Navigation

Home
imorfus (Complete Intranet Solution)
The Alchemist - India
Mix Match Parade
Developers Logic
Speaking Dodo
Programmer's Logic
i-Vista Digital Solutions Pvt Ltd

Recent Posts


On this page....


Archives

 Full Archives By Category
 2007 Calendar View
November, 2009 (1)
August, 2009 (1)
July, 2009 (1)
June, 2009 (1)
May, 2009 (2)
April, 2009 (1)
March, 2009 (2)
February, 2009 (5)
January, 2009 (6)
December, 2008 (12)
November, 2008 (2)
<September 2010>
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

Categories

CDOSYS (1) Classic ASP (10) Command Line (2) Databases (16) Excel (1) HTML (1) IIS (10) Indexing Service (1) Internet Explorer (7) Media Streaming (1) MS.Net (2) SQA (7) SQL Server (16) Windows OS (2)
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Blogroll - Fav Blogs

 Geek Speak
 Gyan Guru

Acknowledgments

DasBlog Theme Design by: Tom Watts
E-mail: Send mail to the author(s)
Theme Image by: dreamLogic

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Technology Blog

« Using Advanced Stream Redirector (ASX) t... | Main | How to determine the size of a table in ... »
# Friday, February 06, 2009
Understanding and Using PIVOT clause in SQL Server 2005
SQL Server 2005 has lots of new features, of which one is PIVOT clause. This is similar to Pivot tables in MS Excel and Matrix Control in Reporting Service. Here is a simple example demonstrating how to use PIVOT clause. I am right now taking a simple example. There are scenarios where there could be a need to write a big stored procedure to build a Pivot table in lower versions of SQL Server.

Do run the below query in Query Analyzer to create the table "BillsByQuarter" having Year, Quarter, and Bill Amount as column names.

SET NOCOUNT ON

CREATE TABLE dbo.BillsByQuarter
(
theYear INT,
theQuarter INT,
theBillAmount INT,
PRIMARY KEY (Y,Q)
)
GO

Run the below query to add few records in the table "BillsByQuarter"

INSERT dbo.BillsByQuarter(theYear, theQuarter, theBillAmount)
SELECT 2007, 2, 79000
UNION SELECT 2007, 3, 21000
UNION SELECT 2007, 4, 24000
UNION SELECT 2008, 1, 12000
UNION SELECT 2008, 2, 24000
UNION SELECT 2008, 3, 42000
UNION SELECT 2008, 4, 87000
UNION SELECT 2009, 1, 34000
GO

The query below would sum the Bill Amount for each Quarter for all years

SELECT theYear,
Q1 = SUM(CASE WHEN theQuarter =1 THEN theBillAmount END),
Q2 = SUM(CASE WHEN theQuarter =2 THEN theBillAmount END),
Q3 = SUM(CASE WHEN theQuarter = 3 THEN theBillAmount END),
Q4 = SUM(CASE WHEN theQuarter = 4 THEN theBillAmount END)
FROM
dbo.BillsByQuarter
GROUP BY theYear
ORDER BY theYear
GO

Same results can now be achieved using PIVOT clause.

SELECT theYear,
[1] AS Q1,
[2] AS Q2,
[3] AS Q3,
[4] AS Q4
FROM
(SELECT theYear, theQuarter, theBillAmount
FROM dbo.BillsByQuarter) s
PIVOT
(
SUM(theBillAmount)
FOR theQuarter IN ([1],[2],[3],[4])
) p
ORDER BY [theYear]
GO

Databases | SQL Server
Friday, February 06, 2009 9:09:00 PM (India Standard Time, UTC+05:30)
#  |  Akash Heranjal  |  Disclaimer  |  Comments [0]  |  Trackback Related posts:
How to limit the data returned by each columns in a SELECT statement?
How to swap data between two columns of a table in SQL Server?
Smarter the better – SQL Server tips – part 03
Smarter the Better – SQL Server Tips – Part 02
Smarter the Better – SQL Server Tips – Part 01
Finding Stored Procedure Create and Modified Date in SQL Server
All comments require the approval of the site owner before being displayed.
Name
E-mail
(will show your gravatar icon)
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, sup, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview