4 Replies Latest reply on Apr 5, 2013 7:09 PM by user8187496

# Problem grouping rows to create an id

Hi all,
I have one table(tableA) with a column called sorted_rows wich contains imported data.
The rows looks like this,
20AC0982034LMCODE
60asldkf0984LMCODE
20AC09820tyuLMCODE
60asfkfj098674LMCODE
20ACrtut982rt4LMCODE
40ASDFJ34875CODE
50SDLFK345345
60asldkrtyyy4LMCODE
20ACrtERT2rt4LMCODE
40AGGHM75CODE
50GFHJ345345
60aslFGHFGHyy4LMCODE
70LKDJFG34225

One item is represented by the two first digits 20-70, the imported data is sorted so that one item for example is
20AC0982034LMCODE
60asldkf0984LMCODE
the next item
20AC09820tyuLMCODE
60asfkfj098674LMCODE
and so on
the last item in the above example is
20ACrtERT2rt4LMCODE
40AGGHM75CODE
50GFHJ345345
60aslFGHFGHyy4LMCODE
70LKDJFG34225

What i need and can't figure out how to do is:
starting from the first row in tableA i want to look at the two first digits 20-70 to create an item_id
insert into tableB with an extra column item_id

for example,
tableA
20AC0982034LMCODE
60asldkf0984LMCODE
20ACrtERT2rt4LMCODE
40AGGHM75CODE
50GFHJ345345
60aslFGHFGHyy4LMCODE
70LKDJFG34225

new table with item_id
20AC0982034LMCODE 101001
60asldkf0984LMCODE 101001
20ACrtERT2rt4LMCODE 101002
40AGGHM75CODE 101002
50GFHJ345345 101002
60aslFGHFGHyy4LMCODE 101002
70LKDJFG34225 101002

Best regards
Mikael
• ###### 1. Re: Problem grouping rows to create an id
search start_of_group keyword. there is more questions and solutions about your question

Edited by: Ramin Hashimzadeh on Apr 5, 2013 11:16 PM
• ###### 2. Re: Problem grouping rows to create an id
Hi, Mikael,

Welcome to the forum!

Whenever you have a problem, please post CREATE TABLE and INSERT statements for your sample data. for example:
``````CREATE TABLE     tablea
(   sorted_rows          VARCHAR2 (30)
,   sort_num          NUMBER (10)      PRIMARY KEY
);

INSERT INTO tablea (sorted_rows, sort_num) VALUES ('20AC0982034LMCODE',    1);
INSERT INTO tablea (sorted_rows, sort_num) VALUES ('60asldkf0984LMCODE',   2);
INSERT INTO tablea (sorted_rows, sort_num) VALUES ('20ACrtERT2rt4LMCODE',  3);
INSERT INTO tablea (sorted_rows, sort_num) VALUES ('40AGGHM75CODE',         4);
INSERT INTO tablea (sorted_rows, sort_num) VALUES ('50GFHJ345345',         5);
INSERT INTO tablea (sorted_rows, sort_num) VALUES ('60aslFGHFGHyy4LMCODE', 6);
INSERT INTO tablea (sorted_rows, sort_num) VALUES ('70LKDJFG34225',         7);``````
Remember that rows in a table have no built-in order. For words like "first" or "next" to have any meaning, you have to say what order you mean. I'll assume you have another column in tablea (sort_num) that determines the order.

When does a new item begin? I'm guessing that it's when the first 2 characters of sorted_rows come before the first 2 characters of the previous sorted_rows (where "previous" is determined by sort_num).

Here's one way to do that:
``````WITH     got_new_item     AS
(
SELECT     sorted_rows, sort_num
,     CASE
WHEN  SUBSTR (sorted_rows, 1, 2) >=
SUBSTR ( LAG (sorted_rows) OVER ( ORDER BY  sort_num)
, 1
, 2
)
THEN  0
ELSE  1
END          AS new_item
FROM    tablea
)
SELECT    sorted_rows, sort_num
,       10100 + SUM (new_item) OVER (ORDER BY  sort_num)     AS item_id
FROM       got_new_item
ORDER BY  sort_num
;``````
Output:
``````SORTED_ROWS                      SORT_NUM    ITEM_ID
------------------------------ ---------- ----------
20AC0982034LMCODE                       1      10101
60asldkf0984LMCODE                      2      10101
20ACrtERT2rt4LMCODE                     3      10102
40AGGHM75CODE                           4      10102
50GFHJ345345                            5      10102
60aslFGHFGHyy4LMCODE                    6      10102
70LKDJFG34225                           7      10102``````
If you want to create a table that contains this result set, say:
``````CREATE TABLE  tableb
AS
WITH     got_new_item     AS
(
SELECT     sorted_rows, sort_num
...``````
If you want to add rows to an existing tableb, then use MERGE. Exactly how to do that depends on your exact requriements. Will the item_ids always start with 10101? If not, how do you determine the first item_id to use? Will you have to check if rows from tablea have already been inserted into tableb? Is so, how will you know if two rows are the same or not? Give a couple of examples, like this:
"If tablea contains ... and tableb contains ... before combining them, then tableb should contain ... after combining them, because .... Then, if tablea changes to ... and I combine them again, then tableb should contain ... because ...".

See the forum FAQ {message:id=9360002}
1 person found this helpful
• ###### 3. Re: Problem grouping rows to create an id
WHAT VERSION!!!!!

there is a feature called substr... been around since version 1?2? In 11gR2, there is also a feature called a "derived" column that does not consume but can be indexed using a function-based index.

alter table a add columnb generated always as substr(co1,1,2);

This can be done on the fly if necessary.

You know, simple questions like this can be very easily determined if one was to actually RTFineM!!!!!! You better hope that your boss doesn't search these forums to see who can't do their job and having someone else do their work for them. If you don't know how to RTFM, then you are in the wrong profession.
• ###### 4. Re: Problem grouping rows to create an id
Hi Frank,
Thank you for the swift reply, your reply looks very helpfull.

Since i'm not at work anymore my database access is limited.
I will return with results on monday.

Also thank you for the hints about making better posts.

Best regards
Mikael