Microsoft SQL group by to get multidimensional array

Question

I'm using PHP and Microsoft SQL 2003.

I have this table:

Page | Group
------------
   1 |    1
   2 |    2
   3 |    1
   4 |    2

I need to get an array grouped by "Group":

Array( 
  1 => Array(1, 3), 
  2 => Array(2, 4)
);

I am using PDO, I'd like to get the array directly from SQL instead of parse the entire result with a PHP loop.

How can I do?

this is the full array, and the key to generate the groups is obviously "group" — Fez Vrasta, Oct 22 '13 at 14:29
@JoeFletch It is pretty clear from the question and the returned data what the OP is trying to achieve. As far as returning an array from an SQL statement goes, you cannot do this unless you use a stored procedure... — Ben Carey, Oct 22 '13 at 14:30
Sorry for the confusion. I missed it! I will remove my comments! — JoeFletch, Oct 22 '13 at 14:38

score 2 · Accepted Answer · answered Oct 22 '13 at 14:39

2

You cannot return multidimensional array from an SQL statement. You can of course use stored procedures to return multiple recordsets but returning multidimensional arrays is not possible.

I therefore suggest something like the following solution (not tested):

$stmt = $pdo->prepare("SELECT Page,Group FROM SOME_TABLE");

$array = array();
if($stmt->execute()) {
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $array[$row['Group']][] = $row['Page'];
    }
}

This will group the page numbers into their separate groups, and therefore return an array like:

Array( 
  1 => Array(1, 3), 
  2 => Array(2, 4)
);

answered Oct 22 '13 at 14:39

Ben Carey

16,540
19
87
169

Thanks. Is it not possible in any SQL engine or just on Microsoft SQL? – Fez Vrasta Oct 22 '13 at 14:43
It is not possible in any SQL engine as you cannot return an array! You could return a comma separated list, but to do even this you would need a view or a stored procedure. You **cannot** pass an array from SQL to PHP, they are different languages for a start... – Ben Carey Oct 22 '13 at 14:44
Thank you, so looks like I'll use your snippet. – Fez Vrasta Oct 22 '13 at 14:45
@FezVrasta See here: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – Ben Carey Oct 22 '13 at 14:46
AAHH!! I knew there was a way! Damn Microsoft! Thanks – Fez Vrasta Oct 22 '13 at 14:49
@FezVrasta Please be aware that this will not return an array! It will return a comma separated list. You can then use `explode` in PHP to create an array from it. You would still be better off using the solution I have suggested as it is cleaner and much less complicated, therefore easier to maintain :-) – Ben Carey Oct 22 '13 at 14:51
Get almost all the work done by SQL instead by PHP I think is easy and has best performances. – Fez Vrasta Oct 22 '13 at 15:03

Microsoft SQL group by to get multidimensional array

1 Answers1