sql server - SQL--Regroup all the records based on their unique ID -


i have table following:

groupid     id      name   address   aaa   bbb  ccc    other columns    1      00111     aaa     -----    ---   ---  ---    ------------    1      00122     bbb     -----    ---   ---  ---    ------------    1      00133     ccc     -----    ---   ---  ---    ------------    2      00144     ddd     -----    ---   ---  ---    ------------    2      00155     eee     -----    ---   ---  ---    ------------    3      00111     aaa     -----    ---   ---  ---    ------------    3      00177     ggg     -----    ---   ---  ---    ------------    4      00188     ppp     -----    ---   ---  ---    ------------    4      00199     ooo     -----    ---   ---  ---    ------------    4      00177     ggg     -----    ---   ---  ---    ------------

so records have same group id potential duplicate records. records same id may appear in different groups. need regroup them based on id,and make them appear once in table.

if id'00111'is in group 1 , group 3, 2 groups' records have regroup , have unique groupid. note in group 3, id '00177' in group 4 well, group 3 , 4 have regroup.

so result following:

groupid     id      name   address   aaa   bbb  ccc    other columns  1+3+4    00111     aaa     -----    ---   ---  ---    ------------  1+3+4    00122     bbb     -----    ---   ---  ---    ------------  1+3+4    00133     ccc     -----    ---   ---  ---    ------------  1+3+4    00177     ggg     -----    ---   ---  ---    ------------  1+3+4    00188     ppp     -----    ---   ---  ---    ------------  1+3+4    00199     ooo     -----    ---   ---  ---    ------------    2      00144     ddd     -----    ---   ---  ---    ------------    2      00155     eee     -----    ---   ---  ---    ------------

it challenging , table has on 3 million records, 2 million unique ids. thinking add 1 more column master group id flag records , merge them. example provided, '00111' in group 1 , 3 group 1 , 3 has combine, in group 3 other record '00177' appears in group 4, group 1,3 , 4 have combine. seems big chain never end.

any suggestions appreciated.

thanks

not sure if understood question, can try following:

select sum(groupid) groupid, id, name, address, aaa, bbb, ccc, ... mytable group id, name, address, aaa, bbb, ccc, ...

continue select column list remainder of columns.


Comments

Post a Comment

Popular posts from this blog

matlab - Deleting rows with specific rules -

i got 20*3 cell array , need delete rows contains "137", "2" , "n:t" origin data: 't' '' '' 'np(*)' '' '' [ 137] '' '' [ 2] '' '' 'are' 'and' 'np(fcc_a1#1)' '' '' '1:t' [ 1200] [0.7052] '' [1.2051e+03] [0.7076] '' 'are' 'and' 'np(fcc_a1#3)' '' '' '2:t' [ 1200] [0.0673] '' [1.2051e+03] [0.0671] '' 'are' 'and' 'np(m23c6)' '' '' '3:t' [ 1200] [0.227...
Read more

php - MySQLi multi_query results for later use -

i using mysqli multi_query work several select statemets @ time. know how handle results, able use them later in code. example: <?php //connection stuff $query = "select name, surname database1;"; $query.= "select car, year, type database2 carid='1';"; $query.= "select product, price database3;"; if ($mysqli->multi_query($query)) { if($result = $mysqli->store_result()) { while($row = $result->fetch_row()) { --> here? } } } ?> <html> <div id='persona'> <?php foreach() { --> print name + surname } ?> </div> <div id='cars'> <?php foreach() { --> print car + year + type } ?> </div> <div id='product'> <?php foreach() { --> print product + price } ?> </div> </html> one more thing, prepared statements not possible when using multiple_query, right? there no benefit in putting unrelated queries in 1 multi ...
Read more