Group Header in SQL Query

Question

I have a table like this,

ID    Description     City
----------------------------------
001   free_text       MUL
002   also_free_text  ERL
003   another_text    MUL
004   whatever_text   BER  
005   text            ERL
006   another         BER
007   another_again   MUL

I wonder how can I write a query to get result like this,

City ID     Description
----------------------------------
BER 
     004    whatever_text
     006    another
ERL
     002    also_free_text
     005    text  
MUL
     001    free_text
     003    another_text
     007    another_again

I'm working with Access 2007 with a SharePoint list. I need design the query in Access 2007, so that I can utilize the query elsewhere (not directly in Access, in other words, I cannot use the report function in Access 2007.

Any suggestion? Thanks!

Answer 1

Imagine a table Sequence exist with a single column SeqNo exists, with two rows { {1}, {2} }.

Now we can create the query QueryInner as :

Select
     City   
    ,SeqNo  
    ,ID 
    ,Description 

    ,iif(SeqNo=1, City, " ")  as zCity
    ,iif(SeqNo=2,ID, " ") as zID
    ,iif(SeqNo=2,Description, " ") as zDescription

from data, Sequence

order by 
     City
    ,SeqNo
    ,ID
    ,Description

and the query Query as:

SELECT 
    City
    ,SeqNo,
    max(QueryInner.zCity)          as zCity, 
    max(QueryInner.zID)    as zID, 
    max(QueryInner.zDescription) as zDescription
FROM QueryInner
group by
    City
    ,SeqNo
    ,zID
;

which yields the following structure, from which the required columns can be projected: