Anjum Anjum - 4 months ago 26
SQL Question

PIVOT operator not working in SQL Server 2008 R2

I have SQL Server 2008 R2. I am trying to convert one row to column to get result in desired format.

Table name :

GenderYearWise


Year Gender Total
---------------------
2005 Female 374
2005 Male 1579
2006 Female 853
2006 Male 4769


I want to get result in following format :

Gender 2005 2006
----------------------
Female 374 853
Male 1579 4769


I am trying to get required format using following query :

Select
Gender, 2005, 2006
From
GenderYearWise
PIVOT
(sum(total)
for year IN ([2005], [2006])
) AS PitvotTable


But above query is returning these results :

Gender (No column name) (No column name)
--------------------------------------------
Female 2005 2006
Male 2005 2006


Can anyone please help me and identify the issue in my query?

Answer

In the SELECT statement add escape [ and ] for the column names 2005 and 2006, like [2005], [2006] will solve the issue.

Sample execution with the given data:

DECLARE @GenderYearWise TABLE ([Year] INT, Gender VARCHAR (100), Total INT);

INSERT INTO @GenderYearWise ([Year], Gender, Total) VALUES 
(2005, 'Female'  , 374),
(2005, 'Male'    ,1579),
(2006, 'Female'  , 853),
(2006, 'Male'    ,4769);

Select 
    Gender, [2005], [2006]
From 
    @GenderYearWise
PIVOT
    (SUM(total)
     FOR [Year] IN ([2005], [2006])
) AS PitvotTable

Result:

Gender  2005    2006
Female  374     853
Male    1579    4769

Also escape column name for the reserved keyword Year.