Concat in sql query using sql anywhere

General
1

I am using a function to inject an sql query to our database to get value for a table.. Everything is workning nice so far but i have a query where i sometimes recieve 2-3 values from a colum which gives me redundant information.. I want to concatenate this to a single row but as i looks i can not use conc() in the query.. We are using sql anywhere.. i have looked in the documentation for it and i am not able to figure out how to solve it.

2

What exactly is the query?
Can you get it to work outside of node-red?
What node-red node are you using?

3

i can not access the code from this computer.. I will look tomorrow and post the query. Don't remember exactlly which node i used but it is a simple connection node where i use the windows driver for the obdc to sql anywhere 17.. query is from 4 tables which i join and where order = x, x is my input from another node.. everything works fine but sql anywhere does not use conc() as of my understanding so i need some other way to solve it.

4

Feed the output of the query into a debug node and show us what you get. Also tell us what you want to get as the result, for the debug output you have shown.

5

bild

This is the output..
i want to have a new row for each different tool name which i have now, but i want to concat the store place in the same collum when everything else is the same

6

Please tell us exactly what you want to get out for the example you have shown, not words describing it, which will always be open to interpretation.

7

bild
bild694Ă—283 8.78 KB

8

I dont think that is possible in standard SQL.

You could do some post processing in node-red.

Add a function node AFTER the SQL node...

const arr = msg.payload; //payload is the array of objects from DB
const parts = []; //results
const reg = {}; //registry monitor
arr.forEach(e => {
  let row = reg[e.part_no];
  if(!row) {
    row = Object.assign({}, e)
    reg[e.part_no] = row;
    parts.push(row)
  } else {
    row.store += ", " + e.store
  }
})
msg.payload = parts;
return msg;

NOTE: You will need to update part_no and store to your field names

  • store should be the field you want to concatenate
  • part_no should be the field you want to group on

Example...

image
image945Ă—746 42.4 KB

1 Like
9

Yes.. that is a solution i have conciderd.. I tried yours and it works fine so thank you for that.. However in at least msql i think you can conc() direct in the query.. I will use this solution for now but i am pretty sure i will have the same problem in the future when i create more tables so i will continue to look for a query solution

10

Do you mean mssql?

MSSQL...

    declare @Yourtable table (part_no varchar(100), store varchar(100));
    insert into @Yourtable values ('123','store a'),('123','store b'),('456','store b'),('456','store c'),('999','store c');
    select part_no, string_agg(store,',') as stores
    from @Yourtable t
    group by part_no;
part_no stores
123 store a,store b
456 store b,store c
999 store c

db<>fiddle here

11

Do you mean MySQL?

MySQL...

    CREATE TEMPORARY TABLE Yourtable (part_no varchar(100), store varchar(100));
    insert into Yourtable values ('123','store a'),('123','store b'),('456','store b'),('456','store c'),('999','store c');
    SELECT part_no, GROUP_CONCAT(store) as stores 
      from Yourtable 
      group by part_no;
    drop table Yourtable;
part_no stores
123 store a,store b
456 store b,store c
999 store c

db<>fiddle here

12

Yes.. unforunatelly for me we are using SQLanywhere 17. and i can not find a similar function to use in their documentation.. It would be nice to do it directlly in the query..

13

Doesn't SQL Anywhere have LIST and XML Path?

  SELECT LIST(name, ', ') FROM cities 
  GROUP BY state;

   SELECT STUFF((SELECT ', ' + name FROM cities WHERE state = c.state FOR XML PATH('')),1,2,'')
   FROM cities c
   GROUP BY state
14

ah. yes.. LIST should work.. Looks like it accepts it at least..
Now i need to figure out how to aggregate everything right.. i get this error:
"Error: [SAP][ODBC Driver][SQL Anywhere]Function or column reference to 'toh_ordernr' must also appear in a GROUP BY"

I am quite new to SQL so i do not have full understanding about every function yet..

15

Any columns you want must be either in a function or the group by or excluded.

In your case, add all cols to the group by (except Lagingsplats) & exclude the Rad column

16

Ah, yes.. of course.. now i works, and a really neat solution.. Many thanks

17

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.