Combining rows in an unstructured table

Question

I have this unstructured table where (1) itemID has multiple receptCodes associated with it. What I need to do is to display each receptCode associated with a distinct orderID in one row. Current table structure is similar to this:

    itemID  | receptString
=============================
    itemID1 | receptString1
    itemID1 | receptString2
    itemID1 | receptString3
    itemID1 | receptString4
    itemID2 | receptString5
    itemID2 | receptString6

Desired output:

    itemID    | receptString | receptString  | receptString | receptString |
    ============================================================================
    itemID1  | receptString1 | receptString2 | receptString3 | receptString4 |
    itemID2  | receptString5 | receptString6 |

What would be the correct way to go about writing the query for this? I tried using multiple CASE statements and GROUP BY but I am struggling with the logic on this one. There can only be a MAX of (5) receptStrings per orderID but can vary between 1 – 5 receptStrings. The receptStrings can be a combination of characters, symbols, and numbers. I’m not concerned if NULLs show up. This is only part of what I need but is what I am struggling with. I'm testing this in both PostgreSQL and Oracle SQL.

* UPDATE *

Thank you everyone for your suggestions. The problem ended up being a little more significant than we originally anticipated (the example table I provided was a small piece of a very large pie) so we decided to take a different approach. Thanks again.

Do you mean `itemID` if you say `orderID` because I don't see `orderID` in your sample data? — Geert Bellekens, Oct 13 '15 at 06:24
@Abootman - See if my [solution](http://stackoverflow.com/a/33096328/5234334) works for you and accept it if it does so the question can be closed. Else specify the problem with it so we can correct it. — Utsav, Oct 13 '15 at 09:10
Note: the original table *is* structured. The result table violates 1NF (receptStringX is a repeating group) — joop, Oct 13 '15 at 10:33

score 1 · Answer 1 · answered Oct 13 '15 at 07:13

Tested in 11g.

Here I am taking a temporary delimiter as ~. If you think that your string char have this, you can use | or chr(10) or chr(13) or any other delimiter. It would work fine.

Also the column names cannot be same as you want. I appended 1,2,3 and so on. You can change them as you want.

    with tbl1 (itemID  , receptString) as(
    select 'itemID1','receptString1' from dual union all
    select 'itemID1','receptString2' from dual union all
    select 'itemID1','receptString3' from dual union all
    select 'itemID1','receptString4' from dual union all
    select 'itemID2','receptString5' from dual union all
    select 'itemID2','receptString6' from dual
    ), tbl2 as(
    select itemid,listagg(receptString,'~') within group (order by itemid) || '~' as concstr
    from tbl1
    group by itemid
    )
     SELECT  tbl2.itemid,REGEXP_SUBSTR(concstr ,'([^~]*)(\~)', 1, 1, NULL, 1 ) as receptString1,
      REGEXP_SUBSTR(concstr ,'([^~]*)(\~)', 1, 2, NULL, 1 ) as receptString2,
      REGEXP_SUBSTR(concstr ,'([^~]*)(\~)', 1, 3, NULL, 1 ) as receptString3,
      REGEXP_SUBSTR(concstr ,'([^~]*)(\~)', 1, 4, NULL, 1 ) as receptString4,
      REGEXP_SUBSTR(concstr ,'([^~]*)(\~)', 1, 5, NULL, 1 ) as receptString5
      from tbl2

First I used listagg to concatenate the columns and added delimiter. Then I used regep_substr to separate them into columns. For columns which do not exist, they would be null as per this output.

Output

 ITEMID     RECEPTSTRING1   RECEPTSTRING2   RECEPTSTRING3   RECEPTSTRING4  RECEPTSTRING5
 itemID1    receptString1   receptString2   receptString3   receptString4   
 itemID2    receptString5   receptString6

score 0 · Answer 2 · answered Oct 13 '15 at 05:57

On Oracle 11g R2 and above, you could use LISTAGG for string aggregation.

SELECT itemID  ,
  LISTAGG(receptString, ' | ') WITHIN GROUP (
ORDER BY receptString) AS receptString
FROM table_name
GROUP BY itemID
ORDER BY itemID;

Have a look at Oracle String Aggregation Techniques for other ways on different database versions.

Remember, this is string aggregation and not PIVOT. You will have the rows aggregated to a single delimited string row.

Vivek S. · Answer 3 · 2017-01-30T13:54:03.243

PostgreSQL

consider following as an example

create table ft (itemid text,receptstring text);
insert into ft values
('itemID1','receptString1'),
('itemID1','receptString2'),
('itemID1','receptString3'),
('itemID1','receptString4'),
('itemID2','receptString5'),
('itemID2','receptString6');

and by using WITH Queries (CTE),string_agg(),regexp_split_to_array()

    WITH CTE
AS (
    SELECT itemid || ',' || string_agg(receptstring, ',' ORDER BY receptstring) col
    FROM ft
    GROUP BY itemid
    ORDER BY itemid          
    )
    ,cte1
AS (
    SELECT regexp_split_to_array(col, ',') AS a
    FROM cte
        )
SELECT a [1] AS itemid 
    ,a [2] receptString
    ,a [3] receptString
    ,a [4] receptString
    ,a [5] receptString -- you can add more if your condition exceed 1-5      
                        -- limit(ex.a [6] receptString  )
FROM cte1

OUTPUT:

itemid  receptstring  receptstring    receptstring   receptstring    receptstring
------- ------------- --------------- --------------- --------------- --------------- 
itemID1 receptString1 receptString2   receptString3   receptString4   NULL
itemID2 receptString5 receptString6   NULL            NULL            NULL

Some another way

Maximum Columns per Table 250 - 1600 depending on column types, What If a particular itemID has more than 1600 or 250 numbers of receptString?. So I suggest you do something like below

Using string_agg() in PostgreSQL

 select "itemID"
       ,string_agg("receptString",',' ORDER BY "receptString") receptString 
 from tbl
 group by "itemID"

which will make only two columns the second column stores all the receptString of a particular itemID

This query is nice. Never used `regexp_split_to_array` but looks like a cool function to start using. Thanks — Utsav, Oct 13 '15 at 09:12

Combining rows in an unstructured table

3 Answers3