Basic example of using PIVOT to pivot table data.

pivot.sql

-- 1. create the table
/****** Object:  Table [dbo].[Population]    Script Date: 3/10/2014 7:47:16 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Population](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[State] [varchar](8) NOT NULL,
	[NumberOfPeople] [int] NOT NULL,
 CONSTRAINT [PK_Population] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_Population_State] UNIQUE NONCLUSTERED 
(
	[State] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-- 2. insert some data
insert into Population ([State], NumberOfPeople) values ('NSW', 7500600)
insert into Population ([State], NumberOfPeople) values ('VIC', 5821300)
insert into Population ([State], NumberOfPeople) values ('QLD', 4708500)
insert into Population ([State], NumberOfPeople) values ('SA', 1682600)
insert into Population ([State], NumberOfPeople) values ('WA', 2565600)
insert into Population ([State], NumberOfPeople) values ('TAS', 514700)
insert into Population ([State], NumberOfPeople) values ('NT', 243700)
insert into Population ([State], NumberOfPeople) values ('ACT', 385600)
insert into Population ([State], NumberOfPeople) values ('TOTAL', 23425700)

select * from Population

-- 3. pivot the data
select 'Population',
	[NSW] as 'New South Wales',
	[VIC] as 'Victoria',
	[QLD] as 'Queensland',
	[SA] as 'South Australia',
	[WA] as 'Western Australia',
	[TAS] as 'Tasmania',
	[NT] as 'Northern Territory',
	[ACT] as 'Australian Capital Territory',
	[TOTAL] as 'Total'
from (
	select State, NumberOfPeople from Population
) as unpivoted
pivot
(
	sum(NumberOfPeople)
	for State in ([NSW], [VIC], [QLD], [SA], [WA], [TAS], [NT], [ACT], [TOTAL])
) as pivoted