simple or not?

[federico]

The simple(?) problem is:

We have a Table 'Batch', with 2 columns: Batch and State, with this data:

Batch State
A 3
A 1
A 1
B 2
B 2
B 3
C 1
C 1
C 2


and we want this return

Batch State 1 State 2 State 3
A 2 0 1
B 0 2 1
C 2 1 0

counting the number of lines in each state grouped by Batch

Which is the SQL command to do that?, simple or not?

 

[Sylvain Lafontaine]

Is the number of states 1, 2 and 3 fixed or not?

If it's fixed, a simple Group By associated with a 3 Sum() over 3 Case
statements will do the trick, otherwise it's more complicated; as SQL-Server
doesn't offer a direct support for Pivot transformation.