SQL Group By in to separate result tables

Question

Im in the process of creating a report and I have a table like below

EmployeeName |  Department |    Zone |

Joseph       |   IT        |    1    |

Jack         |   IT        |    3    |

John         |   IT        |    2    |

James        |   IT        |    3    |

Jashua       |   IT        |    1    |

Jingle       |   IT        |    2    |

Sam          |   HR        |    4    |

Sid          |   HR        |    5    |

Steve        |   HR        |    6    |

Sal          |   HR        |    5    |

Stephen      |   HR        |    6    |

Signa        |   HR        |    4    |

Result set should be in following format

Department  | Zone  

IT          | 1

EmployeeName

Joseph

Jashua



Department |    Zone

IT         |    2


EmployeeName

John

Jingle


Department | Zone

IT         | 3


EmployeeName

Jack

James



Department  |  Zone
HR          |  4

EmployeeName

Sam

Signa



Department  |  Zone

HR          |  5


EmployeeName

Sid

Sal


Department  |  Zone

HR          |  6


EmployeeName

Steve

Stephen

How can I achieve this? Thanks

Answer 1

The result of an SQL select statement is always a table (or a single value), but never a dynamic set of tables. I'd suggest to use a query like

select department, zone, employename
from my table
order by department, zone

In SQL, as the result is a single table, you won't have any separators when department or zone change. However, if you generate a report using some other programming language, you could walk through your result and watch for changes in department and zone and enter a horizontal line.

In pseudocode, this could look as follows:

prevDept='';
prevZone='';
while (hasNextRecord()))
{
  record = getNextRecord();
  if (prevDept <> record.department OR prevZone <> record.zone)
    writeHorizontalLine();
  prevDept = record.departnemt;
  prevZone = record.zone;
}