php - Divide a table's column as different rows with mysql -

-

here's table: table1

  id | id_activity | id_elem | text  ---------------------------------------   1  |      1      |    11   | text1  |   2  |      1      |    12   | text2  |   3  |      1      |    13   | text3  |   4  |      2      |    11   | text4  |   5  |      2      |    12   | text5  |    6  |      2      |    13   | text6  |   7  |      3      |    11   | text7  |    8  |      3      |    12   | text8  |   9  |      3      |    13   | text9  |   10 |      4      |    11   | text10 |    11 |      4      |    12   | text11 |   12 |      4      |    13   | text12 |   13 |      5      |    11   | text13 |   14 |      5      |    12   | text14 |    15 |      5      |    13   | text15 |   16 |      6      |    11   | text16 |   17 |      6      |    12   | text17 |    18 |      6      |    13   | text18 |

i need make result this:

  id | text_elem_11 | text_elem_12 | text_elem_13  ---------------------------------------------------   1  |    text1     |    text2     |    text3     |   2  |    text4     |    text5     |    text6     |   3  |    text7     |    text8     |    text9     |   4  |    text10    |    text11    |    text12    |   5  |    text13    |    text14    |    text15    |    6  |    text16    |    text17    |    text18    |

what right way this? follow query can table first , second column

select table1.id,        table1.id_elem,        elem_11.text text_elem_11  table1  inner join table1 elem_11 on table1.id_activity = 1 ,  table1.id_elem = 11 ,  elem_11.id_elem = 11

this result

  id_activity | id_elem  | text_elem_11 |  -----------------------------------------        1      |    11    |    text1     |        1      |    11    |    text4     |        1      |    11    |    text7     |

i don't know how add others 2 columns , if idea single query... so, idea?

select  id_activity,         max(case when id_elem = 11 text end) text_elem_11,         max(case when id_elem = 12 text end) text_elem_12,         max(case when id_elem = 13 text end) text_elem_13    tablename group   id_activity order   id_activity

output

╔═════════════╦══════════════╦══════════════╦══════════════╗ ║ id_activity ║ text_elem_11 ║ text_elem_12 ║ text_elem_13 ║ ╠═════════════╬══════════════╬══════════════╬══════════════╣ ║           1 ║ text1        ║ text2        ║ text3        ║ ║           2 ║ text4        ║ text5        ║ text6        ║ ║           3 ║ text7        ║ text8        ║ text9        ║ ║           4 ║ text10       ║ text11       ║ text12       ║ ║           5 ║ text13       ║ text14       ║ text15       ║ ║           6 ║ text16       ║ text17       ║ text18       ║ ╚═════════════╩══════════════╩══════════════╩══════════════╝

if have unknown number of id_elem, dynamic sql more preferred,

set @sql = null;  select  group_concat(distinct         concat('max(case when id_elem = ',                id_elem,                ' text else null end) ',                concat('`text_elem_' , id_elem, '`')                )) @sql tablename order id_elem;    set @sql = concat('select   id_activity, ', @sql, '                         tablename                     group   id_activity                     order   id_activity');  prepare stmt @sql; execute stmt; deallocate prepare stmt;

Comments

Post a Comment

Popular posts from this blog

java - Jmockit String final length method mocking Issue -

-
using jmockit 1.2 jar, trying mock string's length method getting unexpected invocation exception: failed: test java.lang.illegalstateexception: missing invocation mocked type @ point; please make sure such invocations appear after declaration of suitable mock field or parameter @ stringdemo.testa$1.<init>(testa.java:17) @ stringdemo.testa.test(testa.java:13) i using testng: @test public void test() throws exception { new expectations() { @mocked("length") string astring; { astring.length(); result = 2; } }; system.out.println(a.showa("test")); } } actual class a: public class { public static int showa(string str){ int a= str.length(); return a; } } this wrong way of recording expected results. shouldn't mock , record string's length() method, record showa() instead. here solution @suppresswarnings("unused...
Read more

What is the difference between data design and data model(ERD) -

-
i have google question , find answer in day. know, data design many many table have list column,and mark pk , fk. why er-diagram have find this? data design process of designing database. main output of data design detailed logical data model of database. some people data design includes of needed logical , physical design choices , physical storage parameters needed generate design in data definition language. technically, database analysis, database analysts (dbas) trained do. while person can both data design , database analysis, these 2 different tasks. data design models data. database analysis takes model , applies 1 or more database engines (relational, hierarchical, nosql). a logical data model 1 of main outputs of data design. data model represented entity relationship diagram, or er diagram.
Read more

ios - Can NSManagedObject conform to NSCoding -

-
i need transfer single object across device. right converting nsmanagedobject dictionary , archiving , sending nsdata. upon receiving unarchiving it. transfer nsmanagedobject archiving , unarchiving instead of creating intermediate data object. @interface test : nsmanagedobject<nscoding> @property (nonatomic, retain) nsstring * title; @end @implementation test @dynamic title; - (id)initwithcoder:(nscoder *)coder { self = [super init]; if (self) { self.title = [coder decodeobjectforkey:@"title"]; //<crash } return self; } - (void)encodewithcoder:(nscoder *)coder { [coder encodeobject:self.title forkey:@"title"]; } @end nsdata *archivedobjects = [nskeyedarchiver archiveddatawithrootobject:testobj]; nsdata *objectsdata = archivedobjects; if ([objectsdata length] > 0) { nsarray *objects = [nskeyedunarchiver unarchiveobjectwithdata:objectsdata]; } the problem above code is. crashes @ self.title in initwithcoder sa...
Read more