Look at GROUP BY and the GROUP_BY() function.

Gerry

hm - sorry but I can't see how GROUP BY could help.

Sorry, I meant "GROUP_CONCAT()"

last=-1
first=1
EXEC SQL SELECT id, name FROM t
WHILE (!END OF ROWS)
 FETCH INTO :id, :name
 if id != last
  last = id
  if first
    first=0
    print "id", "name1", "name2"
  else
    print
  end
  print id, name,
 else
  print name,
 end
LOOP
print

Then, of course, you may have misstated your goal and instead of wanting to know how to "display" the data, you want to make a new table-form (which is, obviously, quite a different thing from "display"):

create table t
(
    id  integer not null,
    name text not null
);
insert into t values
    (4,'PRO7'),
    (4,'ProSieben'),
    (5,'RTL'),
    (6,'kabel eins'),
    (6,'KABEL1'),
    (7,'RTL II'),
    (7,'RTL2'),
    (8,'ARTE');
  select id,
         max(iif(cn == 1, name, NULL)) as name1,
         max(iif(cn == 2, name, NULL)) as name2
    from (
           select id,
                  row_number() over (partition by id) as cn,
                  name
             from t
         )
group by id;

Beautiful. You beat me to it :D

It's called a PIVOT operation:

https://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1

For the record, since I typed it as well:

with
DataSet
as
(
    select 4 as id, 'PRO7' as name union all
    select 4 as id, 'ProSieben' as name union all
    select 5 as id, 'RTL' as name union all
    select 6 as id, 'kabel eins' as name union all
    select 6 as id, 'KABEL1' as name union all
    select 7 as id, 'RTL II' as name union all
    select 7 as id, 'RTL2' as name union all
    select 8 as id, 'ARTE' as name
),
ResultSet
as
(
    select  DataSet.*,
            row_number() over( partition by id order by name ) as rn
    from    DataSet
)
select      ResultSet.id,
            max( case when ResultSet.rn = 1 then ResultSet.name end ) as name1,
            max( case when ResultSet.rn = 2 then ResultSet.name end ) as name2
from        ResultSet
group by    ResultSet.id
order by    ResultSet.id;

4|PRO7|ProSieben
5|RTL|
6|KABEL1|kabel eins
7|RTL II|RTL2
8|ARTE|

This is a nice answer and works really well. I'm just trying to understand how it works.

The thing that is confusing me is the purpose of the 'max' function - I note that I can swap it out for a 'min' or 'group_concat' function - and everything continues to work.

So is the use of 'max' (or 'min', 'group_concat') just a way of fitting-in with the group-by ?

Thanks !

You are using a group by, and each "group" contains one value for each column somewhere amongst the group, and null everywhere else. The aggregate ignores null values so will return only the one value (which is also the min and the max and group_concat and perhaps others (that is, aggregates that return one (or only) of the non-null values provided)).

Thanks, that works perfectly.

Do SQLite allows such syntax?

Thanks to all for your input.