Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Location
    Dallas, Texas USA
    Posts
    4

    Angry Unanswered: mySQL Data Manipulation

    I wholeheartedly apologize up front if this explanation is lacking in anyway, but hopefully the CURRENT TABLE and DESIRED TABLE examples below make the explanation clearer.

    Simply put, I am using mySQL Version: 3.23.38 and I am trying to data manipulate specific unique column data for different rows for the same ID to one ID row with this multiple row column data in different columns for each of the IDs.

    Specifically in the CURRENT TABLE example below, for ID1, I have 4 unique Text fields (Memo1.1, Memo1.2, Memo1.3, Memo1.4) of various lengths (1 Bit to many Memos with many KBs of data) that I would like to data manipulate to separate column headings.

    Each Memo for each ID is uniquely identified by a unique IDD alpha numeric code with ID being the primary key.

    Again, what I would like to do is data manipulate these multiple row Memos into multiple columns, so that for each ID, I have all these Memos in different unique columns using the same text column name (Text1) for Memo1 for all IDs that have the first Memo1 and another text column name (Text2) for Memo2 for all IDs that have a second Memo2, etc as illustrated in the DESIRED TABLE below.

    Any advice you could provide would be of great assistance as I have been trying to solve this problem for several days to no avail and I need to have the data in a unique columnar format to automate publishing this database table to a website using a web publishing suite which requires this format in the website layout editor. Please take care and thanks in advance for any assistance you could provide in helping me solve this problem.

    CURRENT TABLE
    ID IDD Memo
    -- --- -------
    1 1.1 Memo1.1
    1 1.2 Memo1.2
    1 1.3 Memo1.3
    1 1.4 Memo1.4
    2 2.1 Memo2.1
    2 2.2 Memo2.2
    3 3.1 Memo3.1
    4 4.1 Memo4.1
    4 4.2 Memo4.2
    4 4.3 Memo4.3
    4 4.4 Memo4.4


    DESIRED TABLE
    ID Text1 Text2 Text3 Text4
    -- ------- ------- ------- -------
    1 Memo1.1 Memo1.2 Memo1.3 Memo1.4
    2 Memo2.1 Memo2.2 <Null> <Null>
    3 Memo3.1 <Null> <Null> <Null>
    4 Memo4.1 Memo4.2 Memo4.3 Memo4.4


    Regards,
    John

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    please specify the below queries:

    - do you know the maximum number of memo for 1 ID? (can find by a simple SELECT sql statement with COUNT)

    - will you do the conversion one time or many times?

    generally, this can be done by vb codes
    qha_vn

  3. #3
    Join Date
    May 2003
    Location
    Dallas, Texas USA
    Posts
    4

    Talking

    qha_vn:

    Thanks so much for your quick reply to my mySQL Data Manipulation post.

    Currently, the number of memos range from none to 10, but that could change with future daily updates to the website requiring this conversion many times each and everyday where the number of memos for each ID could be reduced or increased in the update.

    Basically, I am using a note field in a database client to populate a website as this database client does not have the necessary user-defined fields to input these data, but when I do queries on this table the memos associated with this note field are put in a many row same one column field instead of a one row many column fields which I need in my layout so that I can assign these column field expressions to a particular location in my layout which will pull these data for each unique ID.

    My previous experiences with RDMS's is more as a quality assurance consultant on the design-side ensuring functionality requirements are met without much experience querying or manipulating data in the database or in programming (Visual Basic, or any other programming language) in general.

    I have tried numerous data manipulation (SELECT, JOIN, REPLACE, LOAD DATA INFILE, etc..) as well as creating another table and trying to alter these many row columns of text to the desired one row many columns of text to no avail.

    I have even contemplated a manual "cut and paste" of the data to the desired format but have hesitated for obvious reasons.

    Please know my appreciation in taking your time to reply to my post.

    Regards,
    John

  4. #4
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1

    Re: mySQL Data Manipulation

    Originally posted by mysql
    I wholeheartedly apologize up front if this explanation is lacking in anyway, but hopefully the CURRENT TABLE and DESIRED TABLE examples below make the explanation clearer.

    Simply put, I am using mySQL Version: 3.23.38 and I am trying to data manipulate specific unique column data for different rows for the same ID to one ID row with this multiple row column data in different columns for each of the IDs.

    Specifically in the CURRENT TABLE example below, for ID1, I have 4 unique Text fields (Memo1.1, Memo1.2, Memo1.3, Memo1.4) of various lengths (1 Bit to many Memos with many KBs of data) that I would like to data manipulate to separate column headings.

    Each Memo for each ID is uniquely identified by a unique IDD alpha numeric code with ID being the primary key.

    Again, what I would like to do is data manipulate these multiple row Memos into multiple columns, so that for each ID, I have all these Memos in different unique columns using the same text column name (Text1) for Memo1 for all IDs that have the first Memo1 and another text column name (Text2) for Memo2 for all IDs that have a second Memo2, etc as illustrated in the DESIRED TABLE below.

    Any advice you could provide would be of great assistance as I have been trying to solve this problem for several days to no avail and I need to have the data in a unique columnar format to automate publishing this database table to a website using a web publishing suite which requires this format in the website layout editor. Please take care and thanks in advance for any assistance you could provide in helping me solve this problem.

    CURRENT TABLE
    ID IDD Memo
    -- --- -------
    1 1.1 Memo1.1
    1 1.2 Memo1.2
    1 1.3 Memo1.3
    1 1.4 Memo1.4
    2 2.1 Memo2.1
    2 2.2 Memo2.2
    3 3.1 Memo3.1
    4 4.1 Memo4.1
    4 4.2 Memo4.2
    4 4.3 Memo4.3
    4 4.4 Memo4.4


    DESIRED TABLE
    ID Text1 Text2 Text3 Text4
    -- ------- ------- ------- -------
    1 Memo1.1 Memo1.2 Memo1.3 Memo1.4
    2 Memo2.1 Memo2.2 <Null> <Null>
    3 Memo3.1 <Null> <Null> <Null>
    4 Memo4.1 Memo4.2 Memo4.3 Memo4.4


    Regards,
    John
    Why don't you have something like:

    SOURCE
    SOU_ID
    SOU_COL1
    SOU_COL2

    SOURCE_MEMOS
    SOM_SOU_ID
    SOU_MEM_ID

    MEMOS
    MEM_ID
    MEM_TEXT


    In this case, you can have as many memos as you want for every entry of SOURCE.

    SOURCE_MEMOS tables does a N to N link between table SOURCE and MEMOS.

    Is that what you wanted?

  5. #5
    Join Date
    May 2003
    Location
    Dallas, Texas USA
    Posts
    4

    Talking

    bstjean:

    Thanks for your post which I have been looking at off and on since you first posted it this afternoon and I feel I understand conceptually what you are driving at (leaving you to be the judge and jury on this point), but could you expand on your post as I am not sure where to start to have the fields SOU_COL1 and SOU_COL2 to be in the correct format for the web publishing software to publish the table automatically to a website which requires all these note fields to all be in the same row under their unique column headings.

    I currently have a COMPANY_NOTES table that includes the fields you have outlined in Figure1. Below containing these SOURCE and MEMOS tables, and I can CREATE a SOURCE_MEMOS table, but I am not sure how to make this table an N to N link between tables SOURCE and MEMOS unless you mean through the relational relationships: SOURCE.SOU_ID=SOURCE_MEMOS.SOM_SOU_ID and
    SOURCE_MEMOS.SOU_MEM_ID=MEMOS.MEM_ID.

    --------------------------------------------------------------------------
    Figure 1. bstjean

    SOURCE
    SOU_ID
    SOU_COL1
    SOU_COL2

    SOURCE_MEMOS
    SOM_SOU_ID
    SOU_MEM_ID

    MEMOS
    MEM_ID
    MEM_TEXT

    In this case, you can have as many memos as you want for every entry of SOURCE.

    SOURCE_MEMOS tables does a N to N link between table SOURCE and MEMOS.

    Is that what you wanted?
    ---------------------------------------------------------------------------

    Simply put, I need to have a column heading MEM_TEXTA for the first note in all the records MEM.ID and a column heading MEM_TEXTB for the second note in all the records MEM.ID, etc as this web publishing software reads a the row with the understanding that all the data is in this one row for that MEM.ID under the fields MEM_TEXTA, MEM_TEXTB,.

    Specifically, if the table was in the format in Figure 2, I could then automatically layout all the field values (Mem.id1, mem_texta1, mem_textb1,) under the field headings MEM.ID, MEM_TEXTA, MEM_TEXTB, MEM_TEXTC, which is not the case as my data is in the format of Figure 3.

    Figure 2. Desired Table
    MEM.ID MEM_TEXTA MEM_TEXTB MEM_TEXTC.
    Mem.id1 mem_texta1 mem_textb1 mem_textc1
    Mem.id2 mem_texta2 mem_textb2 mem_textc2
    Mem.id3 mem_texta3 mem_textb3 mem_textc3

    Figure 3. Undesired (Current) Table
    Mem.id1 mem_texta1
    Mem.id1 mem_textb1
    Mem.id1 mem_textc1
    Mem.id2 mem_texta2
    Mem.id2 mem_textb2
    Mem.id2 mem_textc2
    Mem.id3 mem_texta3
    Mem.id3 mem_textb3
    Mem.id3 mem_textc3

    Again I appreciate your assistance, bstjean, and hope my explanation above is not to long-winded as all I am trying to do is reformat my MEMOS table MEM_TEXT note data that is currently in many rows for the same MEM.ID to be in just one MEM.ID row with MEM_TEXT columns for all of these notes belonging to each distinct MEM.ID.

    Regards,

  6. #6
    Join Date
    Sep 2002
    Location
    Montreal, Canada
    Posts
    224
    Provided Answers: 1
    Originally posted by mysql
    bstjean:

    Thanks for your post which I have been looking at off and on since you first posted it this afternoon and I feel I understand conceptually what you are driving at (leaving you to be the judge and jury on this point), but could you expand on your post as I am not sure where to start to have the fields SOU_COL1 and SOU_COL2 to be in the correct format for the web publishing software to publish the table automatically to a website which requires all these note fields to all be in the same row under their unique column headings.

    I currently have a COMPANY_NOTES table that includes the fields you have outlined in Figure1. Below containing these SOURCE and MEMOS tables, and I can CREATE a SOURCE_MEMOS table, but I am not sure how to make this table an N to N link between tables SOURCE and MEMOS unless you mean through the relational relationships: SOURCE.SOU_ID=SOURCE_MEMOS.SOM_SOU_ID and
    SOURCE_MEMOS.SOU_MEM_ID=MEMOS.MEM_ID.

    --------------------------------------------------------------------------
    Figure 1. bstjean

    SOURCE
    SOU_ID
    SOU_COL1
    SOU_COL2

    SOURCE_MEMOS
    SOM_SOU_ID
    SOU_MEM_ID

    MEMOS
    MEM_ID
    MEM_TEXT

    In this case, you can have as many memos as you want for every entry of SOURCE.

    SOURCE_MEMOS tables does a N to N link between table SOURCE and MEMOS.

    Is that what you wanted?
    ---------------------------------------------------------------------------

    Simply put, I need to have a column heading MEM_TEXTA for the first note in all the records MEM.ID and a column heading MEM_TEXTB for the second note in all the records MEM.ID, etc as this web publishing software reads a the row with the understanding that all the data is in this one row for that MEM.ID under the fields MEM_TEXTA, MEM_TEXTB,.

    Specifically, if the table was in the format in Figure 2, I could then automatically layout all the field values (Mem.id1, mem_texta1, mem_textb1,) under the field headings MEM.ID, MEM_TEXTA, MEM_TEXTB, MEM_TEXTC, which is not the case as my data is in the format of Figure 3.

    Figure 2. Desired Table
    MEM.ID MEM_TEXTA MEM_TEXTB MEM_TEXTC.
    Mem.id1 mem_texta1 mem_textb1 mem_textc1
    Mem.id2 mem_texta2 mem_textb2 mem_textc2
    Mem.id3 mem_texta3 mem_textb3 mem_textc3

    Figure 3. Undesired (Current) Table
    Mem.id1 mem_texta1
    Mem.id1 mem_textb1
    Mem.id1 mem_textc1
    Mem.id2 mem_texta2
    Mem.id2 mem_textb2
    Mem.id2 mem_textc2
    Mem.id3 mem_texta3
    Mem.id3 mem_textb3
    Mem.id3 mem_textc3

    Again I appreciate your assistance, bstjean, and hope my explanation above is not to long-winded as all I am trying to do is reformat my MEMOS table MEM_TEXT note data that is currently in many rows for the same MEM.ID to be in just one MEM.ID row with MEM_TEXT columns for all of these notes belonging to each distinct MEM.ID.

    Regards,
    Not sure to get what you mean here but my guess is that you somehow want to order the texts for display? So you need to know their sequence for so you'd need to someting like MEM_SEQUENCE which would store the sequence of every memo...

    Am I right?

  7. #7
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    In my opinion, this may only be achieved by vb codes

    just trying to draw a path below:

    for ease of understanding, provided that the maximum memos one could have is 50, so we will reserve 50 (columns) fields for the memos (mem_textA1, mem_textB1, mem_textC1 ... and so on)

    - create a table (here named DESIRED_TABLE) with 51 fields something likes MEM_ID(primary key), MEM_TEXTA, MEM_TEXTB......

    - create a new sub-routine in vb, here named CONVERT_ROW_TO_COLUMN()

    - read all MEM_ID into a recordset variable (named rst1) with UNIQUE RECORD turned on (this will come up with a list of unique MEM_IDs)

    - start a for... next loop from 1 to rst1.recordcount

    - the (undesired current) table is now called UNDESIRED_TABLE

    - in every single step of the loop, run a SQL statement to filter the UNDESIRED_TABLE for the MEM_ID find in rst1!MEM_ID to find rows of memos of only one member and then set the result to a recordset variable rst2

    - start another for... next loop from 1 to rst2.recordcount (if rst2.recordcount > 50 then trap the error)

    - in every single step of the loop, copy the memo text to appropriate field in the DESIRED_TABLE

    - DONE

    this is only the outline of what we need to do in the coding

    hope this helps
    qha_vn

  8. #8
    Join Date
    May 2003
    Location
    Dallas, Texas USA
    Posts
    4

    Smile

    bstjean and qha_vn:

    Thanks for the excellent posts and suggestions which I am looking at in greater detail and will post how this problem was solved, hopefully, in the not to distant future.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •