Need help with PIVOT 2008 R2

[DougP]

I have a table called “AllBand” with this sample data and columns.
I want to have Personnel Name in column 1 and have subsequent column headings for each date
With total hours listed under the correct Date heading
Sample raw data
Report 1|Personnel Name| Band | WE | TotalHours
Spacely, Mister| Flintstone, Fred| 7T| 11/10/2012| 40
Spacely, Mister| Flintstone, Fred| 7T| 11/10/2012| 34
Spacely, Mister| Flintstone, Wilma| 7T| 11/10/2012| 38
Spacely, Mister| Flintstone, Pebbles| 7T| 11/17/2012| 23

Sample output
Personnel Name 11/10/2012 11/17/2012
Flintstone, Fred 40 34
Flintstone, Wlima 38
Flintstone, Pebbles 23

Get error on this following code
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Personnel Name'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'Personnel Name'.

SELECT  [Personnel Name],
[11/3/2012] AS '11/3/2012',
[11/10/2012] AS '11/10/2012', 
[11/17/2012] AS '11/17/2012',
[11/24/2012] AS '11/24/2012'
FROM 
(SELECT [WE], [Personnel Name],[Total Hours]
from ALLBand) p
PIVOT
(
SUM([Total Hours])
FOR [Personnel Name] IN
( [11/3/2012],[11/10/2012], [11/17/2012],[11/24/2012])
) AS pvt
ORDER BY pvt.[Personnel Name];

DougP

 

[philhege]

Not sure exactly why, but this worked for me. You can adjust it to your situation, and then understand why it works.

create table rawdata
([report 1] varchar(32)
,[personnelname] varchar(32)
,[weekmarker] varchar(3)
,[WE] date
,[totalhours] int)

insert into rawdata
select
'Spacely, Mister', 'Flintstone, Fred', '7T', '11/10/2012', 40
UNION 
SELECT
'Spacely, Mister', 'Flintstone, Fred', '7T', '11/10/2012', 34
UNION 
SELECT
'Spacely, Mister', 'Flintstone, Wilma', '7T', '11/10/2012', 38
UNION 
SELECT
'Spacely, Mister',' Flintstone, Pebbles', '7T', '11/17/2012', 23




SELECT  [personnelname] ,

isnull([11/3/2012],0) as '11/3/2012',
isnull([11/10/2012],0)as '11/10/2012', 
isnull([11/17/2012],0)as '11/17/2012',
isnull([11/24/2012],0)as '11/24/2012' 
FROM 

(SELECT [WE], [personnelName],[TotalHours]
from rawdata) as d

PIVOT
(
SUM([TotalHours])
FOR [WE] IN
( [11/3/2012],[11/10/2012], [11/17/2012],[11/24/2012])
) AS pvt
order by pvt.personnelname

-----------
With business clients like mine, you'd be better off herding cats.

 

[philhege]

Hint: I think it has to do with your PIVOT statement.

-----------
With business clients like mine, you'd be better off herding cats.