Crosstab Query

[C4rtm4N]

I'm new to SQL server & am trying to use DTS to create what amounts to a crosstab query on a text file that I'm importing. I'm aware of having to use the CASE statement to get what I want under normal circumstances but what I need is unfortunately a bit more complicated than normal & I can't get it to work.

The temp table that I've created has the following fields with several transactional records per person per day -

Staff no. Data Date Work type Data1 Data2
123456 24/02/2005 A 1 0
123456 21/02/2005 B 0 1
123456 21/02/2005 B 1 0
123456 20/02/2005 C 0 1

The output that I want will be something like with one line per person per day

[Staff no.] [Data Date] [A Data1] [A Data2] [B Data1] etc (4 data types, 2 lots of data per type)

Anyone give me a pointer ?

Thanks

Steve

 

[clapag22]

select staffno, datadate,
sum(case when worktype = 'a' then data1 else 0 end) as adata1,
sum(case when worktype = 'a' then data2 else 0 end) as adata2,
sum(case when worktype = 'b' then data1 else 0 end) as bdata1,
sum(case when worktype = 'b' then data2 else 0 end) as bdata2,
sum(case when worktype = 'c' then data1 else 0 end) as cdata1,
sum(case when worktype = 'c' then data2 else 0 end) as cdata2,
sum(case when worktype = 'd' then data1 else 0 end) as ddata1,
sum(case when worktype = 'd' then data2 else 0 end) as ddata2
from testtable
group by staffno, datadate

Hope this helps,
Craig

 

[C4rtm4N]

Fantastic Craig - Thanks