Skip to content

How To: Summarize Aggregated Data Using Pivot in TSQL

October 29, 2008

This question posted on StackOverflow.com caught my attention. This is a common data requirement specially in Data Analysis. It goes like this:

I have a table like as follows:

SoftwareName    Count    Country
Project         15       Canada
Visio           12       Canada
Project         10       USA
Visio           5        USA

How do I query it to give me a summary like…

SoftwareName    Canada    USA    Total
Project         15        10     25
Visio           12        5      17

How to do in T-SQL?

Here’s my solution to the problem – A Walkthrough:

Let’s create our test table (“SoftwareDemo”)

 USE [Test]
GO

CREATE TABLE [dbo].[SoftwareDemo](
    [SoftwareName] [nvarchar](50) NOT NULL,
    [Count] [intNOT NULL,
    [Country] [nvarchar](50) NOT NULL
ON [PRIMARY]

Then, let’s insert the sample data:

INSERT INTO SoftwareDemo VALUES ('Project', 15, 'Canada')
INSERT INTO SoftwareDemo VALUES ('Visio', 12, 'Canada')
INSERT INTO SoftwareDemo VALUES ('Project', 10, 'USA')
INSERT INTO SoftwareDemo VALUES ('Visio', 5, 'USA')

 

Here’s our PIVOT query:

SELECT Softwarename, Canada, USA, Canada + USA AS TOTAL FROM SoftwareDemo 
    PIVOT 
    (
     SUM([Count])
     FOR Country
     IN (Canada, USA)
    ) AS x

 

SoftwareName                                       Canada      USA         Total
-------------------------------------------------- ----------- ----------- -----------
Project                                            15          10          25
Visio                                              12          5           17

(2 row(s) affected)

(1 row(s) affected)

 

Here’s our PIVOT TSQL in action:


UPDATE: 02/04/09

Here’s another good example of PIVOT’ing:

http://benchmarkitconsulting.com/colin-stasiuk/2009/01/28/pivot-i-said-pivot/

kick it on DotNetKicks.com

Advertisements
No comments yet

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

  • Archives


  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: