1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Issue inserting multiple rows into one row

Discussion in 'SQL PL/SQL' started by martinez77, Aug 12, 2014.

  1. martinez77

    martinez77 Guest

    Hello all,

    I am simply trying to insert multiple rows into on row. One insert value is hard coded the rest are pulled from a table.

    So am i trying to insert some values into one row and have been unable to unearth any solution. I would appreciate any help.

    So all I want to do is insert the following code into one row of a table that i have created. As of right now it creates 8 rows instead of 1.

    Code:

    insert into cdy_list (list_code)
    select * from (
    select '<GLOBALLIST name=' || '"' || 'Products' || '">'
    from products
    where rownum = 1

    union all select
    '<LISTITEM value=' || '"' || name || '"/>'
    from products
    where visible = 'Y'

    union ALL select
    '</GLOBALLIST>'
    FROM PRODUCTS
    WHERE ROWNUM = 1) ;

    thanks in advance,

    Martinez
    :mad::confused:
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    UNION won't work as it produces multiple rows, one for each unioned query. You also list only one column; there have to be more columns to insert. You need to make the select generate ONE row; please check that the following code produces one row:


    Code (SQL):



    INSERT INTO cdy_list (list_code)
     SELECT * FROM (
     (SELECT '<GLOBALLIST name=' || '"' || 'Products' || '">'
     FROM products
     WHERE rownum = 1),
     (SELECT '<LISTITEM value=' || '"' || name || '"/>'
     FROM products
     WHERE visible = 'Y'),
     (SELECT
     '</GLOBALLIST>'
     FROM PRODUCTS
     WHERE ROWNUM = 1)) ;