Combine multiple results into one row/cell

Sweep_The_Leg
Sweep_The_Leg Member
in Magic ETL

How to take multiple results and combine them into one row, see attached. 

Best Answer

  • AS
    AS Coach
    Answer ✓

    Is that your error message from the NetSuite SQL engine or connector?

    Unfortunately I'm not familiar with how NetSuite has implemented SQL and a quick Google search doesn't reveal much, either.

    Just to be sure, that GROUP_CONCAT function does have an option delimiter parameter, so you might try adding that (like GROUP_CONCAT(b.REGIONS,',').  They might also call that function something slightly different, if it exists, like GROUPCONCAT().

     

    An alternative would be to take it as-is into Domo and do a denormalization and then regular CONCAT() all those columns into one in a dataflow.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach

    There's a GROUP_CONCAT function in SQL that would do this well for you.  I don't think the same exists in ETL in Domo.

     

    https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

     

     

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Sweep_The_Leg
    Sweep_The_Leg Member

    Looks like GROUP_CONCAT isn't a recognized function on NetSuite's version of SQL. Thoughts?

     

    SELECT
    A. TRACKING_ID,
    GROUP_CONCAT (b. REGIONS) as 'REGION'

     

    FROM TRACKING_REGIONS_MAP A

     

    left join REGIONS b on a. TRACKING_ID = b. REGIONS_ID

     

    GROUP BY

    A. TRACKING_ID

     

     

    All columns of the select list & order by should have set functions or be part of GROUP BY clause.[10136] 
     
  • AS
    AS Coach
    Answer ✓

    Is that your error message from the NetSuite SQL engine or connector?

    Unfortunately I'm not familiar with how NetSuite has implemented SQL and a quick Google search doesn't reveal much, either.

    Just to be sure, that GROUP_CONCAT function does have an option delimiter parameter, so you might try adding that (like GROUP_CONCAT(b.REGIONS,',').  They might also call that function something slightly different, if it exists, like GROUPCONCAT().

     

    An alternative would be to take it as-is into Domo and do a denormalization and then regular CONCAT() all those columns into one in a dataflow.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Sweep_The_Leg
    Sweep_The_Leg Member

    The message came from the connector. I tried the various changes you mentioned, but no success. I'm trying it now in MySQL. the function GROUPBY_CONCAT seems to exist because it poopulates itself like a beastmode, but whenever I drop it in, i get: FUNCTION GROUPBY_CONCAT does not exist

     

    I'll keep at it, but thanks for the help!

  • user09445
    user09445 Member

    can someone from Domo look into this function GROUPBY_CONCAT and fix it soon? There're people already mentioned that this function did not work using Mysql methiond in Workflow creatio process in the thread. How come this is not yet being solved as it's a critical SQL aggregation function? I also got the error message - Unknown column 'GROUPBY_CONCAT' in 'field list' or 'GROUPBY_CONCAT'  does not exist during my multiple attempts

  • user09445
    user09445 Member

    This is my script and it did not work instead gave me error message saying function groupby_concat did not exsit. 

    SELECT `customers_id`, GROUPBY_CONCAT (`warehouse`)
    FROM `orders`
    GROUP BY `customers_id`;

  • Sweep_The_Leg
    Sweep_The_Leg Member

    try this:

     

    SELECT
    `Customer Name`,
    GROUP_CONCAT(`Customer States`) AS 'States'
    FROM
    `mb_test_Customer`

    group by `Customer Name`

  • user09445
    user09445 Member

    continue this as example, how do I build further with this script as if to comma seperate customer states in one row, aggreated by customer name?

    I would assume the current script would only work for 1 customer name assigned to 1 uniue state vs multiple states. 

  • Sweep_The_Leg
    Sweep_The_Leg Member

    Like this?

     

    AcmeColoardo
    AcmeIdaho
    AcmeCalifornia
    AcmeNew Mexico
    AcmeNew York
      
    result 
      
    AcmeColoardo,Idaho,California,New Mexico,New York,
  • user09445
    user09445 Member

    that's right. That would be what I would like to achieve. Any solution?

  • Sweep_The_Leg
    Sweep_The_Leg Member

    that's how I was able to do it MySql. 

     

    SELECT
    `Customer Name`,
    GROUP_CONCAT(`Customer States`) AS 'States'
    FROM
    `mb_test_Customer`

    group by `Customer Name`

Categories