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] [int] NOT 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:

[Cross-Posted from my other blog http://dbalink.wordpress.com - Marlon Ribunal ]