Following query is shorter and more easily maintainable :
select *
from (select rack,cell,qty from TestForAccess) as D
pivot(sum(qty) for cell in ([X],[Y],[Z],[M])) as P;
you still have to know up front the cells though, I am looking for a solution for this without resorting to a stored procedure.
if you want flexibility this is the way to do it :
DECLARE @query NVARCHAR(4000)
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',['+ cell + ']','['+ cell + ']') from (select distinct cell from TestForAccess) as T
print @cols
SET @query = 'select rack,'+@cols+' from (select rack,cell,qty from TestForAccess) as D pivot(sum(qty) for cell in ('+@cols+')) as P;'
print @query
execute(@query)
ok how to do this without pivot so that it works in sql 2000
select rack,
SUM(case when cell='X' then qty end) as x,
SUM(case when cell='Y' then qty end) as y,
SUM(case when cell='Z' then qty end) as z,
SUM(case when cell='M' then qty end) as m
from TestForAccess
group by rack