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