I'm very new to Oracle and am more familiar with Microsoft's SQL so I will do my best to state my question in a generic manner. I have a table of defect such as the one created below.
Create table defects (defected uniqueidentifier, inject varchar, detect varchar);
insert into defects values (1, ‘requirements’, ‘design’);
insert into defects values (2, ‘requirements’, ‘design’);
insert into defects values (3, ‘design’, ‘design’);
insert into defects values (4, ‘design’, ‘implement’);
insert into defects values (5, ‘implement’, ‘implement’);
insert into defects values (6, ‘implement’, ‘test’);
insert into defects values (7, ‘implement’, ‘support’);
insert into defects values (8, ‘plan’, ‘requirements’);
insert into defects values (9, ‘plan’, ‘plan’);
insert into defects values (10, ‘manage’, ‘design’);
insert into defects values (11, ‘manage’, ‘manage’);
insert into defects values (12, ‘manage’, ‘requirements’);
insert into defects values (13, ‘test’, ‘test’);
insert into defects values (14, ‘test’, ‘manage’);
insert into defects values (15, ‘design’, ‘design’);
insert into defects values (16, ‘requirements’, ‘requirements’);
insert into defects values (17, ‘implement’, ‘integrate’);
insert into defects values (18, ‘integrate’, ‘integrate’);
insert into defects values (19, ‘test’, ‘test’);
I want to generate a crosstab or pivot table to create the following table.
inject detect
plan manage requirements design implement integrate test
plan 1 0 1 0 0 0 0
manage 0 1 1 1 0 0 0
requirements 0 0 1 2 0 0 0
design 0 0 0 2 1 0 0
implement 0 0 0 0 1 1 1
integrate 0 0 0 0 1 1 0
test 0 1 0 0 0 0 2
but have only been able to generate the following table. Note that I have the column heading in order of execution by phase but the rows are alphabetically sorted. How do I get the table above?
inject detect
plan manage requirements design implement integrate test
design 0 0 0 2 1 0 0
implement 0 0 0 0 1 1 1
integrate 0 0 0 0 1 1 0
manage 0 1 1 1 0 0 0
plan 1 0 1 0 0 0 0
requirements 0 0 1 2 0 0 0
test 0 1 0 0 0 0 2
I'm asking because I want to return the data to MS Excel using ODBC. The diagonal should be all of the places where a defect was detected in the same phase as it was injected. All of the counts above the diagonal are normal cases; such as injecting an error in requirements and detecting it in test. All of the counts below the diagonal are cases that need to be investigated since the data indicates it was detected prior to being injected. The data in the second results is not wrong it is just not visually useful.
order by case inject
when 'plan' then 1
when 'manage' then 2
when 'requirements' then 3
....
when 'test' then 7
end;
the same way you would do it in MS SQL Server actually - you need to have an order by, in this case, you want to order by something that has meaning only to you - so you need to 'teach us' how to do it.
By the way - if your MS SQL server statement doesn't have order by on it, you have a bug in your code - it will change sort order some day, it is only a matter of time. Unless and until a sql statement has order by on it, you cannot expect any data to be returned in any sort of order.