Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    8

    Unanswered: Inserting data from multiple row into one?

    I have two tables

    one with names info and so on
    and one with categorys like this

    ID NAME CATEGORYALL
    1 name1
    2 name2


    and the other
    ID NAMEID CATEGORY
    1 1 category1
    2 1 category2
    3 1 category3
    4 2 category2
    5 2 category4

    and i like to insert a value in the first table
    that will come from multiple rows in the category table
    and look like this

    ID NAME CATEGORYALL
    1 name1 category1,category2,category3
    2 name2 category2,category4


    Please help

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Inserting data from multiple row into one?

    Probably the easiest way is to write a procedural program to do that, something like (this is PL/SQL):

    Code:
    DECLARE
      v_nameid NUMBER := -1; 
      v_categoryall VARCHAR2(4000);
    BEGIN
      FOR r IN (SELECT nameid, category FROM table2 ORDER BY nameid, category)
      LOOP
        IF r.nameid != v_nameid THEN
          IF v_nameid != -1 THEN
            INSERT INTO table1(nameid, categoryall)
              VALUES (v_nameid,v_categoryall);
          END IF;
          v_nameid := r.nameid;
          v_categoryall := r.category;
        ELSE
          v_categoryall := v_categoryall||','||r.category;
        END IF;
      END LOOP;
      /* Catch the last record */
      IF v_nameid != -1 THEN
        INSERT INTO table1(nameid, categoryall)
          VALUES (v_nameid,v_categoryall);
      END IF;
    END;
    There may be syntax errors in the above, I haven't run it, but you may get the idea.

  3. #3
    Join Date
    Feb 2003
    Posts
    8
    Thanks

    But im a beginner
    is there a way to translate this to ms sql2000
    im sorry i didnt write this at first
    Last edited by u31115057; 02-10-03 at 06:18.

Posting Permissions

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