how to group those datas ? 1

[sholine]

Here is a sample of my database :

DATE | OPERATOR
----------------------
2004-08-01 | ope1
2004-08-01 | ope3
2004-08-03 | ope2
2004-08-03 | ope1
2004-08-03 | ope3
2004-08-07 | ope2
2004-08-10 | ope3

("Operator" is an enum of 'ope1', 'ope2', 'ope3')
And I want to obtain something like this :

DATE | ope1 | ope2 | ope3
--------------------------------
2004-08-01 | 1 | 0 | 1
2004-08-03 | 1 | 1 | 1
2004-08-07 | 0 | 1 | 0
2004-08-10 | 0 | 0 | 1

Or more better :

DATE | ope1 | ope2 | ope3
--------------------------------
2004-08-01 | 1 | 0 | 1
2004-08-02 | 0 | 0 | 0
2004-08-03 | 1 | 1 | 1
2004-08-04 | 0 | 0 | 0
2004-08-05 | 0 | 0 | 0
2004-08-06 | 0 | 0 | 0
2004-08-07 | 0 | 1 | 0
2004-08-08 | 0 | 0 | 0
2004-08-09 | 0 | 0 | 0
2004-08-10 | 0 | 0 | 1
(...)

Could somebody help me ?

 

[JarlH]

SELECT DATE,
CASE WHEN OPERATOR = 'ope1' THEN 1 ELSE 0 END AS ope1,
CASE WHEN OPERATOR = 'ope2' THEN 1 ELSE 0 END AS ope2,
CASE WHEN OPERATOR = 'ope3' THEN 1 ELSE 0 END AS ope3
FROM table-name

Note that DATE is a reserved word in SQL. You'd better change that column name. (Or double quote it, like "DATE".)

 

[sholine]

First, thanks a lot for your response...

(No problem with "DATE", I have given this name for the example but the real name is different.)

This solution is interesting but it doesn't correspond exactly to my problem.

But I have forgottent something in my example. Here is the correct example :

DATE | OPERATOR
----------------------
2004-08-01 | ope1
2004-08-01 | ope3
2004-08-03 | ope2
2004-08-03 | ope2
2004-08-03 | ope1
2004-08-07 | ope2
2004-08-10 | ope3

And I want obtain :

DATE | ope1 | ope2 | ope3
--------------------------------
2004-08-01 | 1 | 0 | 1
2004-08-03 | 1 | 2 | 0
2004-08-07 | 0 | 1 | 0
2004-08-10 | 0 | 0 | 1

Observe the column 'ope2' at the '2004-08-03' line.

How could I do that ?

 

[JarlH]

Alright, I think I know what you mean. Try this:

SELECT DATE,
SUM(CASE WHEN OPERATOR = 'ope1' THEN 1 ELSE 0 END) AS ope1,
SUM(CASE WHEN OPERATOR = 'ope2' THEN 1 ELSE 0 END) AS ope2,
SUM(CASE WHEN OPERATOR = 'ope3' THEN 1 ELSE 0 END) AS ope3
FROM table-name
GROUP BY DATE

 

[sholine]

Great ! That's exactly what I want !
Thank you very much !