SQL ROW TO COLUMN

Question

I have a table as follow OPERATIONS

OPERATOR-OPERATION TYPE
Ahmet   DELETE
Ahmet   UPDATE
Ahmet   READ
Salih   DELETE
Salih   UPDATE
Salih   READ
Salih   READ
Salih   CREATE

What SQL will be to get following result? When simple COUNT, GROUP BY clauses used Operation types written as ROW based.

Name- Total- DELETE- READ- UPDATE  CREATE
Ahmet 3      1       1     1       0
Salih 5      1       2     1       1

It depends on your RDBMS. Could you please tag your question with this? This is known as a PIVOT operation - there are quite a few questions on SO relating to this. — Ben, Jun 05 '14 at 13:28

juergen d · Accepted Answer · 2014-06-05T13:39:25.317

1

select operator, 
       count(*) as total,
       sum(case when operation_type = 'DELETE' then 1 else 0 end) as deletes,
       sum(case when operation_type = 'UPDATE' then 1 else 0 end) as updates,
       sum(case when operation_type = 'READ' then 1 else 0 end) as reads,
       sum(case when operation_type = 'CREATE' then 1 else 0 end) as creates
from operations
group by operator

edited Jun 05 '14 at 13:39

answered Jun 05 '14 at 13:29

juergen d

201,996
37
293
362

This may not be the best way if the OP is on a database that supports PIVOT... you're missing total and you haven't capitalised the operation type correctly... – Ben Jun 05 '14 at 13:31
is there a way to automatize the adding of operator type by reading from OPERATION_TYPES table. – Ahmet Karakaya Jun 05 '14 at 13:33
@mmc18 Take a look at this http://stackoverflow.com/a/24057529/2118383 – Vignesh Kumar A Jun 05 '14 at 13:51

SQL ROW TO COLUMN

1 Answers1