# Combining dataset results into single dataset

**URL:** <https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502>\
**Category:** Ignition\
**Tags:** scripting\
**Created:** [January 19, 2024, 2:38pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502 "2024-01-19T14:38:24Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nicholas\_Houston](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas_houston/32/68700_2.png) [@Nicholas\_Houston](https://forum.inductiveautomation.com/u/Nicholas_Houston)\
**Post date:** [January 19, 2024, 2:38pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/1 "2024-01-19T14:38:24Z")

</div>

I'm trying to create a script to combine the results of two different queries. I can't seem to append the results into a single dataset. Here is my script with what it is returning in the script console.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/b/ab0e329732eab1702ba6766c783bfd75457016ce.png)

Pretty much I want to combine the three results into a single dataset in order to view the combined dataset in a table.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 19, 2024, 2:54pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/2 "2024-01-19T14:54:10Z")

</div>

Please post preformatted code, and not pictures of code. Makes it more difficult to help when we have to retype the script.

Normally I would say write a query that returns all of the results that you want. The database is going to be much better at this than a script, however, I don't know what the data looks like or what the stored procedures you're running are actually doing.

You can use the DatasetBuilder to accomplish this.

```auto
from com.inductiveautomation.ignition.common.util import DatasetBuilder

stackId = '3316'

stackDataQuery = 'EXEC dbo.PIR_GetStackData @stackId = ?'
serialDataQuery = 'EXEC dbo.DET_GetSerialData @serial = ?'
stackDataResults = system.db.runPrepQuery(stackDataQuery, [stackId])

builder = DatasetBuilder.newBuilder()
builder.colNames(['column names for your dataset'])
builder.colTypes(['column types for your columns']) #list of column types must be the same length as the headers

for stack in stackDataResults:
  for row in system.db.runPrepQuery(serialDataQuery,[stack]):
      builder.addRow(row)

print builder.build()

```

---

<div class="post-metadata">

**Author:** ![Nicholas\_Houston](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas_houston/32/68700_2.png) [@Nicholas\_Houston](https://forum.inductiveautomation.com/u/Nicholas_Houston)\
**Post date:** [January 19, 2024, 3:32pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/3 "2024-01-19T15:32:51Z")

</div>

Irose,  
I think what you have here will work, however, I can't figure out what to put in for colTypes. Here is a screenshot of a returned value from the stored procedure.

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/9/c9e45409294813d96c2244e4a64bb3f2395adb73.png)

Here is what I am trying.

```auto
from com.inductiveautomation.ignition.common.util import DatasetBuilder
stackId = '3316'

stackDataQuery = 'EXEC dbo.PIR_GetStackData @stackId = ?'
serialDataQuery = 'EXEC dbo.DET_GetSerialData @serial = ?'
stackDataResults = system.db.runPrepQuery(stackDataQuery, [stackId])

builder = DatasetBuilder.newBuilder()
builder.colNames(['Serial', 'Shop', 'Length', 'Width', 'Thickness', 'IsPositivePressure', 'TopRailFinishCode', 'BotRailFinishCode'])
builder.colTypes([str, int, int, int, int, bool, int, int]) #list of column types must be the same length as the headers

for stack in stackDataResults:
  for row in system.db.runPrepQuery(serialDataQuery,[stack]):
      builder.addRow(row)

print builder.build()

```

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 19, 2024, 3:42pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/4 "2024-01-19T15:42:32Z")

</div>

I agree with @lrose here: seems to me like the whole thing could be done in just one query, which would make it simpler and more efficient.  
Can you share that stored proc ?

---

<div class="post-metadata">

**Author:** ![Nicholas\_Houston](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nicholas_houston/32/68700_2.png) [@Nicholas\_Houston](https://forum.inductiveautomation.com/u/Nicholas_Houston)\
**Post date:** [January 19, 2024, 3:58pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/5 "2024-01-19T15:58:23Z")

</div>

The stored procedure is looking at tables that I don't have access to. The client is very strict with the development vs live database, and is writing the stored procedures themselves for me to use.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 19, 2024, 4:17pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/6 "2024-01-19T16:17:55Z")

</div>

Well if they want to write the queries themselves, ask them for a proper query that does what you need it to do ;p

If you haven't solved the dataset merging thing, try this:

```python
from itertools import chain

data = list(chain.from_iterable(
	system.dataset.toPyDataSet(system.db.runPrepQuery(sp, [stack]))
	for stack in stackDataResults
))

```

if you need it in dataset format:

```python
data = [system.dataset.toPyDataSet(system.db.runPrepQuery(sp, [stack]))	for stack in ids]

headers = list(data[0].columnNames)
ds = system.dataset.toDataSet(
	headers,
	list(chain.from_iterable(data))
)

```

Though the builder way is probably better. I just never used it.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 19, 2024, 4:18pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/7 "2024-01-19T16:18:40Z")

</div>

Well, it's expecting Java types. Guess I should have included that since it isn't intuitive from just the script.

this should do it:

```auto
from com.inductiveautomation.ignition.common.util import DatasetBuilder
from java.lang import String, Integer, Boolean
stackId = '3316'

stackDataQuery = 'EXEC dbo.PIR_GetStackData @stackId = ?'
serialDataQuery = 'EXEC dbo.DET_GetSerialData @serial = ?'
stackDataResults = system.db.runPrepQuery(stackDataQuery, [stackId])

builder = DatasetBuilder.newBuilder()
builder.colNames(['Serial', 'Shop', 'Length', 'Width', 'Thickness', 'IsPositivePressure', 'TopRailFinishCode', 'BotRailFinishCode'])
builder.colTypes([String.class, Integer.class, Integer.class, Integer.class, Integer.class, Boolean.class, Integer.class, Integer.class]) #list of column types must be the same length as the headers

for stack in stackDataResults:
  for row in system.db.runPrepQuery(serialDataQuery,[stack]):
      builder.addRow(row)

print builder.build()

```

You can find the documentation [here](https://files.inductiveautomation.com/sdk/javadoc/ignition79/795/com/inductiveautomation/ignition/common/util/DatasetBuilder.html).

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 19, 2024, 4:20pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/8 "2024-01-19T16:20:37Z")

</div>

I guess you could make it a bit simpler by getting the column names and types from one of the datasets ?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 19, 2024, 4:22pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/9 "2024-01-19T16:22:26Z")

</div>

Possibly, but technically you don't have the final datasets until after the builder is constructed. You must call `colNames` first and `colTypes` second, and then add rows. You don't really want to call those functions multiple times.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 19, 2024, 4:30pm UTC](https://forum.inductiveautomation.com/t/combining-dataset-results-into-single-dataset/84502/10 "2024-01-19T16:30:08Z")

</div>

Indeed, but you could run the queries first, get the names and types from one of them, then build the final dataset from those.  
Kinda like this:

```python
from itertools import chain
from com.inductiveautomation.ignition.common.util import DatasetBuilder

data = [system.db.runPrepQuery(sp, [stack]) for stack in stackDataResults]

builder = DatasetBuilder.newBuilder()
builder.colNames(data[0].columnNames)
builder.colTypes(data[0].columnTypes)

for row in chain.from_iterable(data):
	builder.addRow(list(row))

ds = builder.build()

```

Not sure it's really simpler, but at least you don't have to deal with types and names yourself