SQL need help to group my results

Question

Hey guy im looking to display some data from my oracle DB. im looking to group it by common data/column but cant figure it out.

$stid = oci_parse($conn, " SELECT REQ.REQSTN_NO, REQ.WO_NO, REQ.COST_CENTRE_CD, REQ.ACCT_PRIME_CD, REQ.ACCT_SUBSDRY_CD, REQ.STOCK_CD    
                             FROM TE.REQSTNREQ                              
                             WHERE REQ.DEPT_CD='ISN'");
oci_execute($stid);
while (($row = oci_fetch_array($stid, OCI_BOTH+OCI_RETURN_NULLS)) != false) {
  echo $row['COST_CENTRE_CD']."-".$row['ACCT_PRIME_CD']."-".$row['ACCT_SUBSDRY_CD']." ".$row['WO_NO']." ".$row['REQSTN_NO']." ".$row['STOCK_CD']."<br />";
}

Im looking to create an output like this

Ive tried Group BY and SUM/COUNT but i dont know how to structure the code properly any help would be appreciated.

score 0 · Answer 1 · answered Mar 09 '17 at 19:08

This is not a real database "grouping" -- it is a display issue: you want to group rows with common column values together and print each shared column value only once.

Such display issues are best left to the presentation layer of your application and best left out of the SQL/data model layer.

Nevertheless, here is a technique you can use to group common column values together and to print each value only once, using SQL.

(Since you didn't provide your data in text form, this example uses DBA_OBJECTS to illustrate the technique).

SELECT 
       -- Order the row_number () partitions the same way the overall query is ordered...
       case when row_number() over (partition by object_type order by object_type, owner, object_name) = 1 THEN object_type ELSE NULL END object_type,  
       case when row_number() over (partition by object_type, owner order by object_type, owner, object_name) = 1 THEN owner ELSE NULL END owner, 
       object_name,
       created, last_ddl_time 
FROM dba_objects o
ORDER BY
-- Important to qualify columns in ORDER BY... 
o.object_type, o.owner, o.object_name;

The idea is that case statements check to see if this is the first row in a new shared common value and, only if so, to print the column value. Otherwise, it prints NULL.

score -1 · Answer 2 · edited May 23 '17 at 12:09

You would need to use an object-relational database to achieve such a result.

Edited answer:

In MySQL you can use the following function: GROUP_CONCAT:

See reference: https://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

I believe there is a similar solution in oracle. You would need to refer to the following question: Is there any function in oracle similar to group_concat in mysql?

SQL need help to group my results

2 Answers2