simple or not?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

federico - 29 Aug 2005 21:10 GMT

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?

Reply to this Message

Sylvain Lafontaine - 29 Aug 2005 21:31 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

> The simple(?) problem is:
>
[quoted text clipped - 21 lines]
>
> Which is the SQL command to do that?, simple or not?

Reply to this Message