database - SQLITE Query For Composite Data to show in one column for the identical column value -

i have implement 1 complex query in sqlite don't have proficient knowledge in sqlite.

i have 1 table named pupiltestanswers in have composite primary key

my schema follows:

create table pupiltestanswers (   testquestionid integer not null,   pupilid integer not null,   testid integer not null,   score integer not null default(-2),   lastupdated text,   ismyscore integer not null,   isuploaded integer not null,   primary key(testquestionid, pupilid) ); 

now data follows:

now data want give me list of data should unique combination in result.

e.g.

18 |   3 | 2 | 1 | 2016-01-06t06:13:50.000z | 1 | 0 16 | 154 | 2 | 0 | 2016-01-06t06:13:50.000z | 0 | 0 

for above tow row their lastupdate values same same testid need 1 row in result follows row , rows testid , last update different need separate row them

18,16 | 3,154 | 2 | 1,0 | 2016-01-06t06:13:50.000z | 1,0 | 0,0 

so aboe thing want achieve in sqlite .

i have tried in sqlserver can't convert query in sqlite.

i have following sql server query giving me want

;with cte  as( select     *,      dense_rank() over(order testid, lastupdated) rn  pupiltestanswers ) select distinct rn,r.testid, r.lastupdated, r.testname, (stuff((select ','+cast(pupilid varchar(10)) cte  cte.rn=r.rn xml path('')),1,1,'')) pupilid, (stuff((select ','+name  cte  cte.rn= r.rn xml path('')),1,1,'')) name cte r 

i have included inner join testname , pupil name in column id

to 1 output row each unique combination of testid , lastupdated, use group by:

select ... pupiltestanswers group testid, lastupdated; 

(this works in sql database.)

to combine values of multiple rows group, use group_concat function:

select group_concat(testquestionid),        group_concat(pupilid),        testid,        group_concat(score),        lastupdated,        group_concat(ismyscore),        group_concat(isuploaded) pupiltestanswers group testid, lastupdated;