Crosstab Query

[orna]

This is my table

Date	      Shift   Type    Code
--------      -----   ----    ----
03/05/09	1	A	1
03/05/09	1	B	4
03/05/09	1	W	9
03/05/09	1	W	19
03/05/09	2	A	15
03/05/09	2	B	5
03/05/09	2	W	6
03/05/09	2	W	12
04/05/09	1	A	2
04/05/09	1	B	4
04/05/09	1	W	3
04/05/09	1	W	13
04/05/09	2	A	17
04/05/09	2	B	5
04/05/09	2	W	9
04/05/09	2	W	12

I need the query results look like this:
Shift  Type   03/05/09     04/05/09
----   ----   ---------     ---------
1	A	1	      2
1	B	4	      4
1	W	9	      3
1	W	19	      13
2	A	15	      17
2	B	5	      5
2	W	6	      9
2	W	12	      12

Thanks for any help

 

[namliam]

What is it you dont understand about making a crosstab, obviously you know what it is and know how it works... So what is your specific question?

 

[khawar]

Actually you cannot use groupby for value field in crosstab so the solution is a little tricky

Download the attached file and check the query FinalCrosstab

It is based on another query query1

 

[orna]

Khawar, Thanks!

 

[namliam]

Actually you cannot use groupby for value field in crosstab so the solution is a little tricky

Where would you be using a group by for a value field in this?? I dont understand this comment?

 

[khawar]

if you see the result he required he want two row for type w for each date where shift is same but there are more than one codes
In simple crosstab it cannot be achieved directly