Dataset value fetch based on column value

Hello there,

I need help on below scenario:

I’ve a dataset (returning from query) of 2 columns. First column contains duplicate values and other column contains unique values like below.

Col1 Col2
Abc 100
Abc 101
Abc 102
Xyz 200
Xyz 102
Xyz 201
Xyz 202

So how do I do select only values from col2 which has Abc in col1. There’s this huge data of around 20-25k rows.
I know I could do this easily with query like below:
SELECT col2 FROM tbl
WHERE col1 = Abc

But I need to know how to do it with dataset function.

Another thing I’d like to know is, is it possible to merge a rows here?

Thanks in advance

What DB flavor are you using? In mySQL/MariaDB, You can aggregate col2 into a single string like this

SELECT Col1, GROUP_CONCAT(Col2)
FROM Table
GROUP BY Col1

which yields

'Abc' | '100,101,102'

You could also make the dataset a pyDataset and for loop it and append to a list every time col1 = ‘Abc’

I’m using MSSQL for this but I am trying to make this all at view side rather than through query.

Yes i know about pyDataset one but looking for other approaches to do the same. PyDataset loop takes time to return the result, around 10-15 seconds for huge data and more

I am wondering why you don’t want to do this with a query. By not going the query route you are going to be processing your large amount of data twice which is going to take more time. Another possibility is to create a View in your database, but that pretty much doing the same thing as a query.

I’m avoiding to go with query because the dataset I’m returning is fetched from different sources using multiple cursor functions. That puts this combined value in temp table all together in the temporary table and returns it as my result dataset

@pturmel has a Simulation Aids Module that is free and includes an expression function that can search a dataset like a query called view()

2 Likes

Thanks man, that seems to help!

1 Like