Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    29

    Unanswered: Present data in a new table er view + Update ?!

    Hi all,

    Here is the case. I want to present my data in a new table or view from another table.

    table1 (Existing) / main columns presented
    seq
    item
    main
    sub
    ...
    ..
    .

    I Want to move main, sub column to a new table or create a view. Dont wish is the most efficient way.
    Code:
    CREATE TABLE custom.table2 (
    	obj_num INT NOT NULL DEFAULT AUTOINCREMENT,
    	main CHAR (50),
    	sub CHAR (50)
        )
    After this I insert the values to table2 from table1
    Code:
    INSERT INTO custom.table2 (main, sub)
    SELECT main, sub
    FROM custom.table1
    The result looks like this now.
    Code:
    1, main1, sub1
    2, main1, sub2
    3, main1, sub3
    4, main2, sub1
    ..and so on
    I Want to reorganize this table by like this. main followed by sub i one row. Little unsecue how to create this, have been thinking about using ALTER TABLE, UNION and GROUP BY. Dont know if this is the right way to go.

    Wanted result:
    Code:
    1, main1
    2, sub1
    3, sub3
    4, sub3
    5, main2
    6, sub1
    ..and so on
    Next step below:

    If this is possible. Can I reorganize and update obj_num column and sort this by the main and sub. If I want every main to start by the number '1000' and followed by linked sub straight after ?

    The column main and sub could variate a lot in numbers. So I cant preconfigure the different main and sub to specific obj_num.

    Code:
     
    1000, main1
    1001, sub1
    1002, sub2
    1003, sub3
    2000, main2
    2001, sub1
    ..and so on
    Regards

  2. #2
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    (The problem has nothing to do with table1 vs table2; just table2 and "how to get the report" you want, correct ?)

    You do not need to implement anything "fixed" or preconfigure anything. Forcing a main/sub breakdown by key is not necessary (it is a relational db, all columns are available via SQL).

    You need to Pivot the table, which is not too difficult, and will handle any data volumes. There are many write-ups available on pivoting.

    Separately, table2 is a mere duplicate of table1 (chosen columns), and that is not necessary either, you can generate "what you want" directly from table1. The difficulty is in how "fixed" or natural (related to both main and sub) the key in table1 is; which may be the reason you have created table2 with an Identity column. Yes ?

    What is the EXACT report you want (do not show 1, 2, 3; 1001, 1002, etc unless you actually want it).

    We don't have autoincrement either.
    Last edited by Derek Asirvadem; 11-12-09 at 04:49.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  3. #3
    Join Date
    Oct 2007
    Posts
    29
    Ok, thx for the reply Derek.

    Gonna give you a short briefing what Im trying to accomplish.

    table1 Is just a custom temporary table which data is stored from a big .csv file imported from excel. In this ark there a lot of information stored in different columns. All this columns of data is later on going to be main tables for storing different kinds of information like (item, main, sub, price ..)

    Basically Im pretty much done with my application but i want to improve the GUI interface. For now I just insert the main and sub column from table1 to two different tables like this:

    Code:
    CREATE TABLE custom.main (
    	obj_num INT NOT NULL DEFAULT AUTOINCREMENT,
    	main CHAR (50)
        )
    
    CREATE TABLE custom.sub (
    	obj_num INT NOT NULL DEFAULT AUTOINCREMENT,
    	sub CHAR (50)
        )
    
    INSERT INTO custom.main (main)
    SELECT DISTINCT table1.main
    FROM custom.table1
    WHERE table1.main IS NOT NULL
    
    INSERT INTO custom.sub (sub)
    SELECT DISTINCT table1.sub
    FROM custom.table1
    WHERE table1.sub IS NOT NULL

    This works fine and the result becomes:

    The 1, 2, 3 is the obj_num
    custom.main
    Code:
    1, main1
    2, main2
    3, main3
    ..
    custom.sub
    Code:
    1, sub1
    2, sub2
    3, sub3
    ..
    Im adding a SEQ_NUM to these tables aswell to identify the unique row. Only the obj_num and main/sub is shown in the GUI. The obj_num cant be NULL and has to be UNIQUE and its purpose is to keep order in the table and sorting data in the GUI. This number could be changed in the GUI, by doin this the row just jumps up or down in the list.

    For exampel if I have 99 different sub in one long row (as for now) it would be easier to have this different sub categories sorted by main, like an "HEADER".

    So custom.table2 is basically custom.sub in my previous post. 1000, 1001, 1002 was just an exampel how result could be presented

    I dream scenario from custom.sub would be something like this.

    Code:
    1000, ** Main1
    1001, Sub1
    1002, Sub2
    1999, NULL
    2000, ** Main2
    1001, Sub1
    1002, Sub2
    2999, NULL
    ..


    Regards
    Last edited by mberggren; 11-16-09 at 05:10.

Posting Permissions

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