0

I am trying to convert a table such as seen below in a dynamic way to construct a traceability chain model of batch throughout my process (success of tanks)

After some tests in excel, I went with SQL because I am working on a database and I thought the looping function would be the way. I am looking for help because my code is getting overly complex for me (first project in SQL) and maybe I am missing a simpler solution with or without PostgreSQL.

Right now, I am stuck in the dynamic function that would create a table with as many columns as unique tanks and populate them based on the joins of multiple tables. Any help would be appreciated, Thanks!

Database table:

US tank DS tank US batch n° DS batch n°
citerne B430 122 55
B430 K4 55 603

US means UpStream and DS DownStream

Result table expected:

citerne B430 K4
122 55 603

there is a finite number of tanks but an infinite number of the batch numbers.

Here is a schema of my method : https://i.stack.imgur.com/bl7hx.png

Here is a fiddle of my data and expected result : https://dbfiddle.uk/wG9ghV_P

Here is the result expected from this data (also in the fiddle): https://i.stack.imgur.com/2OQhS.png

Grivel
  • 3
  • 3
  • Do you really need to store the expected result in a table ? This could be the result of a query similar to a crosstab function, but with a variable number of values which makes it more complex. [This solution](https://stackoverflow.com/a/70466824/8060017) should fit your expectation with some adaptations. – Edouard Oct 24 '22 at 19:44
  • I don't think crosstab or other alternative would work because my expected result is not a pivot table : I don't have an common identifier for the row of a pivot table, one batch number is only connected to the next batch number => This is my challenge ! As put in my schema, I think I need to go in two steps : first create my table with the tank as column name in the right sequential order, then populate it with the first tank's (citerne) unique batch number, and fill each row with a looping function : what batch number goes after another for a tank (column) given. – Grivel Oct 25 '22 at 08:24
  • Also to answer your first question, I plan to use the expected result in a data model in power BI to connect data received from tanks. For that, I think I need a result table. – Grivel Oct 25 '22 at 08:30
  • I have reused and adapted my proposed solution to your specific case. See my answer. – Edouard Oct 25 '22 at 09:47

1 Answers1

0

The proposed solution here just requires a dedicated composite type which can be dynamically created and then it relies on the jsonb type and standard functions :

Creating the composite type dynamically :

CREATE OR REPLACE PROCEDURE create_composite_type() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
  SELECT string_agg(DISTINCT quote_ident(t.tank) || ' text', ',')
    INTO column_list
    FROM ( SELECT "US tank" FROM my_table
           UNION ALL 
           SELECT "DS tank" FROM my_table
         ) AS t(tank) ;
  
  EXECUTE 'DROP TYPE IF EXISTS composite_type' ;
  EXECUTE 'CREATE TYPE composite_type AS (' || column_list || ')' ;
END ;
$$ ;

Calling the procedure and executing the right query to get your expected result :

CALL create_composite_type() ;

SELECT (jsonb_populate_record( null :: composite_type
                             , jsonb_object_agg(t.tank, t.batch)
                             )
       ).*
 FROM ( SELECT "US tank" AS tank, "US batch n°" AS batch FROM my_table
        UNION 
        SELECT "DS tank", "DS batch n°" FROM my_table
      ) AS t ;

The result is :

B430 citerne K4
55 122 603

see the test in dbfiddle

Edouard
  • 6,577
  • 1
  • 9
  • 20
  • It works well on the little example provided. Thanks! However, on my real sample of data (see my first post), it only returns one line of batch number and not as many lines as unique chain of batch (see expected result). How could I get as many lines as unique chain of batch (ie batch number connected)? I tried to change the table t to a "union all" and other small adaptation but with no luck. I imagine I need a proper function. Interested in any advice – Grivel Oct 28 '22 at 15:28
  • Sorry I can't help you based on url links to pictures. Please update your question with a more complete exemple and expected result with plain text tables so that I can understand your "real sample of data" and what you expect from. – Edouard Oct 28 '22 at 16:56
  • I have updated my fiddle in the post [link](https://dbfiddle.uk/wG9ghV_P) – Grivel Nov 02 '22 at 15:10
  • I have updated the solution in [dbfiddle](https://dbfiddle.uk/kzvvK1_R) in order to calculate the tanks chains using a recursive query, but I get some more results than what you expect : 162 chains with 'Citerne' as initial tank instead of 12 expected ... I let you analyse the results and adapt the recursive query consequently. Thank you to validate and vote for my answer if it has been helpfull. – Edouard Nov 02 '22 at 17:06