Merge arbitrary number of rows in sqlite3

Sunday, May 4, 2014

Say after populating a database like so, with arbitrary row length, in this case 4;



alpha bravo charlie delta echo foxtrot
1 NULL NULL NULL NULL NULL
NULL 1 NULL NULL NULL NULL
NULL NULL 1 NULL 1 NULL
NULL NULL NULL 1 NULL NULL


But the row count could also be 2 in another case (as the db itself is populated by a loop from the top ten matches from two other dbs, where 1 equals a match, and NULL is for no match) :



alpha bravo charlie delta echo foxtrot
NULL NULL 1 NULL 1 NULL
NULL NULL NULL NULL NULL 1


In each case, the sum of any column, regardless of the number of rows, will only ever add up to 1. And the max number of rows could only ever be 10. Hence the question is, how do i merge these rows into one row in each case, so that the first case would look like:



alpha bravo charlie delta echo foxtrot
1 1 1 1 1 NULL


similarly the second case would become:



alpha bravo charlie delta echo foxtrot
NULL NULL 1 NULL 1 1


and so forth. Is there neat a way to do this, bare in mind that there are 40 plus columns in the database?







http://ift.tt/1hqwOIN