The correct PIVOT query would be something like this
declare @r table (Mobile int, Calldate datetime, Dispo varchar(10))
insert @r select
1, '2011-02-21', 'Code1' union all select
2, '2011-02-22', 'Code2'
SELECT ID, [2011-02-21], [2011-02-22]
-- SELECT * << or just use this, which includes all columns
FROM (
SELECT Id, Date, Code
FROM @r) p
PIVOT (MAX(Code) FOR Date IN ([2011-02-21], [2011-02-22])) AS pvt
ORDER BY ID
Your query used
SELECT [Id], '1-2-2011','2-2-2011'
Which includes two FIXED-VALUE strings, which means the DATA is the string '1-2-2011', not a column name. You also needed MAX(Code)
or some aggregate function to use when pivoting.
Pivoting in SQL Server requires you to know the columns in advances, which you need to list out in the FOR () bit. Otherwise, you will need to look at dynamic pivoting. This is true whether you use the PIVOT operator or the MAX(CASE
pattern to pivot.
The MAX(CASE)
pattern
select id,
MAX(case when date = '2011-02-21' then Code end) "2011-02-21",
MAX(case when date = '2011-02-22' then Code end) "2011-02-22"
from @r
group by id