Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2007
    Posts
    15

    Question Unanswered: convert columns to rows (was "Urgen Question")

    I have a table with some fields

    for example

    ID name1 name2 name3
    1 me you he
    2 she him they

    i need to insert them into another table

    ID No Field
    1 1 me
    1 2 you
    1 3 he
    2 1 she
    2 2 him
    2 3 they

    How can that be done with pl/sql ?

    Best regards

    Pascal Wouters

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    insert
      into newtable
    select ID 
         , 1 as No
         , name1 as Field
      from oldtable     
    union all
    select ID 
         , 2 
         , name2 
      from oldtable     
    union all
    select ID 
         , 3 
         , name3
      from oldtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2007
    Posts
    15

    Exclamation Converting data from 1 file to another is different format

    I have a table (see below)

    ID recfield1 recfield2 recfield3 (table structure)
    1 a b c (data)
    2 d e f (data)

    if need to insert them in another table

    ID no recfield (table structure)
    1 1 a
    1 2 b
    1 3 c
    2 1 d
    2 2 e
    2 3 f

    where ID of new table is Id of old table and no is a counter wich decreases with 1 starting with one for each new id

    Best regards and thans in advance

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try the solution in post #2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2007
    Posts
    15
    Problem is that he is overwriting data in the new table
    maybe it can work with a commit in between ?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    INSERT INTO does not overwrite
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2007
    Posts
    15
    ok, i have this statement

    insert
    into SAS2000_CONTROLE.TESTDETAIL (id, volgnummer, inhoud)
    select ID
    , 1 as No
    , INHOUDVELD02
    from SAS2000_CONTROLE.BOSCH_CHK
    union all
    select ID
    , 2
    , INHOUDVELD03
    from SAS2000_CONTROLE.BOSCH_CHK
    union all
    select ID
    , 3
    , INHOUDVELD04
    from SAS2000_CONTROLE.BOSCH_CHK

    and he only writes into new file (TESTDETAIL) the first select ID, 1 as no

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in that case, it appears that postgresql cannot use a UNION query in the INSERT/SELECT syntax

    so try three separate queries --
    Code:
    insert
      into SAS2000_CONTROLE.TESTDETAIL 
         ( id, volgnummer, inhoud )
    select ID,   1,  INHOUDVELD02
      from SAS2000_CONTROLE.BOSCH_CHK 
    ;
    insert
      into SAS2000_CONTROLE.TESTDETAIL 
         ( id, volgnummer, inhoud )
    select ID,   2,  INHOUDVELD03
      from SAS2000_CONTROLE.BOSCH_CHK 
    ;
    insert
      into SAS2000_CONTROLE.TESTDETAIL 
         ( id, volgnummer, inhoud )
    select ID,   3,  INHOUDVELD04
      from SAS2000_CONTROLE.BOSCH_CHK 
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2007
    Posts
    15

    Thumbs up

    ok i will try that
    other question
    can an if statement be create so that if a field is not filled in it wil not be written in the new table,
    see example

    ID recfield1 recfield2 recfield3 (table structure)
    1 a c (data)
    2 d e f (data)

    if need to insert them in another table

    ID no recfield (table structure)
    1 1 a
    1 2 c
    2 1 d
    2 2 e
    2 3 f

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you know what a WHERE clause is?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2007
    Posts
    15
    Yes i can use that one, but that is not helping me with the counter.
    My old table is having 27 lines this means that my new table should have
    27*3 = 81 lines, but he is know creating 112333 lines

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no need for a counter, as you only have 3 data columns

    as for your 112333 rows, i have no idea, that doesn't sound possible

    perhaps you should drop and recreate your table and start over...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2007
    Posts
    15

    Thumbs up

    I did the drop and create and that is working now
    for my earlier question

    ID recfield1 recfield2 recfield3 (table structure)
    1 a c (data)
    2 d e f (data)

    Gives me the result :

    ID no recfield (table structure)
    1 1 a
    1 3 c
    2 1 d
    2 2 e
    2 3 f

    And not the result

    ID no recfield (table structure)
    1 1 a
    1 2 c
    2 1 d
    2 2 e
    2 3 f

    I am sorry for all the questions but i am rather new in pl/sql and oracle

    thanks in advance for helping me out

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pl/sql? oracle?

    do you realize you've posted into the postgresql forum?

    i'll move this thread to the oracle forum for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2007
    Posts
    15
    So i have to go to the oracle forum than

Posting Permissions

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