0

I have two SQL tables of data extracted from a ERP (invoices) the first contains the "head" of the invoice (invoice_number, date, customer, ...) the second table contains the "rows" of all invoices (good_description, qty, price, ...)

To simplify:

  T1.ID  |  T1.COL_A | T1.COL_B           T2.T1_ID |  T2.COL_A | T2.COL_B
  -----------------------------           ------------------------------- 
   1     |    AAA    |    BBB                 1    |    eee    |    fff
   2     |    CCC    |    DDD                 1    |    ggg    |    hhh
                                              2    |    iii    |    jjj   
                                              2    |    kkk    |    lll

If I make a SELECT joining two tables by ID field it would give:

           T1.COL_A  |  T1.COL_B  |  T2.COL_A | T2.COL_B
          ---------------------------------------------- 
   1          AAA    |    BBB     |    eee    |    fff
   1          AAA    |    BBB     |    ggg    |    hhh
   2          CCC    |    DDD     |    iii    |    jjj   
   2          CCC    |    DDD     |    kkk    |    lll

Ok quite simple.

The problem is that I need a specific format to import the data in another ERP, a kind of CSV format made like this:

    ROW_TIPE    ;  T1.COL_A  ;  T1.COL_B  ;  T2.COL_A ; T2.COL_B
---------------------------------------------------------------- 
  INVOICE_HEAD  ;     AAA    ;    BBB     ;           ; 
  INVOICE_ROW   ;            ;            ;    eee    ;    fff
  INVOICE_ROW   ;            ;            ;    ggg    ;    hhh
  INVOICE_HEAD  ;     CCC    ;    DDD     ;           ;          
  INVOICE_ROW   ;            ;            ;    iii    ;    jjj  
  INVOICE_ROW   ;            ;            ;    kkk    ;    lll

So the rows are "grouped" and not repeated and at the first column is inserted the type of row to be processed by the ERP importing the data (if it's INVOICE_HEAD or INVOICE_ROW)

What's the best way to achieve this result in SQL?

The data is from a Pervasive Btrieve. I managed to extract the invoice data from that in two CSV files (one for each table). Then I imported in a SQLite to make the manipulation needed to get a format that is possible to import in the new ERP software.

closed as unclear what you're asking by ypercubeᵀᴹ, Marco, Colin 't Hart, mustaccio, Michael Green May 21 '17 at 10:45

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • The version of the SQL engine would also useful. You also must define and let us know the ORDER BY clause for the SELECT that JOINS by ID. The SQL language offers no guarantee that T2.COL_A's first row will be 'eee' and T2.COL_A's 2nd row will be 'ggg', even when that is the order with which those two rows were inserted into T2. – Bill May 16 '17 at 5:47
  • Would we be correct in assuming that the values in your file are separated by semi-colon, not comma? You show the semi-colons as their own columns; is that simply to include them in the file? and, ideally, should the padding in your output be maintained, or would the the first data row Ideally be: INVOICE_HEAD;AAA;BBB:eee;fff – RDFozz May 16 '17 at 17:37
  • yes there are semicolons in the expected csv, but isn't important the padding.thanks – Luigi G. May 16 '17 at 23:30
  • The concepts in this answer will serve dba.stackexchange.com/a/174107/36809 – Michael Green May 21 '17 at 10:45
  • Possible duplicate of Can I merge duplicate results into a single result? (SQL) – Michael Green May 21 '17 at 10:45
1

May I suggest to use views? Consider the following tables (and test data):

-- T1
create table ihead (
  id number primary key
, col_a varchar2(32)
, col_b varchar2(32)
);

-- T2
create table irow (
  headid number references ihead(id)
, col1 varchar2(32)
, col2 varchar2(32)
, irowid number primary key
);

In the second table (that you have called T2), there should the rows should be numbered somehow, otherwise it will be hard to get to invoice details into the correct order - as @Bill has hinted.

-- test data
begin
  insert into ihead values (1, 'AAA', 'BBB');
  insert into ihead values (2, 'CCC', 'DDD');
  insert into irow values (1, 'eee', 'fff', 1);
  insert into irow values (1, 'ggg', 'hhh', 2);
  insert into irow values (2, 'iii', 'jjj', 3);
  insert into irow values (2, 'kkk', 'lll', 4);  
end;

I'd create a join first, and store it as a VIEW, like so:

-- join, similar to the first select you have written

create or replace view alldetails
as
select
  H.id, H.col_a, H.col_b, R.col1, R.col2, R.irowid
from 
  ihead H join irow R on id = headid;

Check the output:

select * from alldetails;
-- output
ID COL_A  COL_B  COL1  COL2  IROWID
1  AAA    BBB    eee   fff   1
1  AAA    BBB    ggg   hhh   2
2  CCC    DDD    iii   jjj   3
2  CCC    DDD    kkk   lll   4

Second view: get all INVOICE_HEADS (first select), then INVOICE_ROWS (second select)

create or replace view idetails (c1, c2, c3, c4, irow)
as
select 'INVOICE_HEAD', col_a, col_b, col1, col2 from alldetails
where irowid in (
  select min(irowid) from iheader group by id
)
union
select 'INVOICE_ROW', null, null, col1, col2 from alldetails
where irowid not in (
  select min(irowid) from iheader group by id
)

Now we can just:

select * from idetails order by irow
-- output
C1              C2      C3      C4      IROW
INVOICE_HEAD    AAA     BBB     eee     fff
INVOICE_ROW     -       -       ggg     hhh
INVOICE_HEAD    CCC     DDD     iii     jjj
INVOICE_ROW     -       -       kkk     lll

Get to the final output by executing:

select 
  c1 ||';'|| c2 ||';'|| c3 ||';'|| c4 ||';'|| irow
from idetails
order by irow


-- final output (some padding added manually,
-- but can be done with RPAD or LPAD)

INVOICE_HEAD ; AAA ; BBB ; eee ; fff
INVOICE_ROW  ;     ;     ; ggg ; hhh
INVOICE_HEAD ; CCC ; DDD ; iii ; jjj
INVOICE_ROW  ;     ;     ; kkk ; lll

UPDATE 2017-05-17 (requirements have changed)

-- leave the first view as it is,
-- change the second view to be:
create or replace view idetails (
  c1, c2, c3, id, c4, c5, irowid )
as
select distinct 
  'INVOICE_HEAD', col_a, col_b, headid, null, null, 0 
from alldetails
union
select 
  'INVOICE_ROW', null, null, headid, col1, col2, irowid 
from alldetails;

Then, select all the columns you need (notice: 2 columns in the ORDER BY clause):

select 
  c1 ||';'|| c2 ||';'|| c3 ||';'|| c4 ||';'|| c5
from idetails
order by id, irowid;

Output (padding added by hand, just for clarity):

INVOICE_HEAD;AAA;BBB;   ;
INVOICE_ROW ;   ;   ;eee;fff
INVOICE_ROW ;   ;   ;ggg;hhh
INVOICE_HEAD;CCC;DDD;   ;
INVOICE_ROW ;   ;   ;iii;jjj
INVOICE_ROW ;   ;   ;kkk;lll
  • Unfortunately i've had to change the output csv format as edited, because making tests with a handmade file it wont import... padding not seems to be relevant. anyway i think it's the right direction. thanks Stefan – Luigi G. May 17 '17 at 1:48

Not the answer you're looking for? Browse other questions tagged or ask your own question.