Help with crosstab (was "Query Help Needed!")

Hey,

i have a table which has the foll data:

employeecode Amount AmountDescription
1 100 x
2 200 y
3 150 x
4 300 z

now i need to fetch this data such that i can display the output as :

empcode x y z
1 100
2 200
3 150
4 300

any suggestions???

platform: SQL Server 2000

thanx!sorry, no suggestions.... unless we know

1) why do u need it (the practical scenario)
2) how do u ensure that the string "x" fits a column name
3) how do u ensure that the number of columns is within the max limit for select/table
4) what would be the value against row 1, col x of the output|||I think this will do what you want.

SELECT empcode
, Max(CASE WHEN AmountDescription = 'x' THEN amount ELSE 0 END) As 'x'
, Max(CASE WHEN AmountDescription = 'y' THEN amount ELSE 0 END) As 'y'
, Max(CASE WHEN AmountDescription = 'z' THEN amount ELSE 0 END) As 'z'
FROM MyTable
GROUP BY empcode|||no, george, that will put 0s where they didn't exist in the data|||just tweak that last response to use null values instead of 0's

select empcode, case when AmountDescription = 'x' then amount else null end as X,
Case when AmountDescription = 'y' then amount else null end as Y,
Case when AmountDescription = 'z' then amount else null end as Z
from MyTable
Group by Empcode|||but don't lose your MAXes ;)|||Max means that it becomes aggregated and doesn't have to be used in the GROUP BY clause ;)|||i need to fetch this data such that i can display the output as :
empcode x y z
1 100
2 200
3 150
4 300
SELECT empcode
, Amount
, x = NULL
, y = NULL
, z = NULL
FROM MyTable


:)|||<sigh />

pootle, please forgive the lack of proper spacing in the original post

this is what was intended (and you can see this if you open up the original post in Edit) --

empcode x y z
1 100
2 200
3 150
4 300sql

Help with crosstab

I need to create a crosstab report. I have never done it before and need some help with it. I would appreciate any help and guidance.

I have a report that has the grouping as below

Region
Sector
Interval
Area
Crew

I need to add a crosstab report in the interval group header that will summarize the data by area and crew. I go to Insert crosstab and select Area as my column heading and Crew as my rows. Then I want to use @.PercentComplete formula as the summarized field but I don't see it in available fields and even if I create new formula from within the crosstab window I still don't see it. Any suggestions as to why I am not seeing this formula. Formula is as below

If {@.ScheduledTasks} = 0 then
"N/S"
Else If {@.TotalTasks} = 0 then
"N/D"
Else
cStr( (sum({@.TimelyCOmplete},{@.Group_CrewUnit})/(sum({@.TimelyCOmplete},{@.Group_CrewUnit}) + sum({@.MissedTasks},{@.Group_CrewUnit}) + sum({@.LateComplete},{@.Group_CrewUnit}))) * 100, 2)

Sample data for Crosstab is below

Crew Area1 Area2 Area3 %Complete
AAA 100 100 N/S 97.61
BBB 100 N/S N/S 100.00
CCC 0.00 100 N/S 81.25
DDD N/S 100 N/S 100
EEE N/S 96.87 N/D N/D
-- --- ---
%Complete 98.28 100.00 N/Danyone?