# Advanced SQL Query Help

**URL:** <https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353>\
**Category:** Ignition\
**Tags:** ignition81, ignition80, database, scripting, perspective\
**Created:** [November 10, 2022, 2:47pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353 "2022-11-10T14:47:40Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [November 10, 2022, 2:47pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/1 "2022-11-10T14:47:40Z")

</div>

Hello people,

I want to achieve something like below snip:

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/1/e1e5711438a6d90091bcd2f5612d5dce184ed468.png)

How can I do it through SQL? I am using MSSQL.

Note that Col4 is also there for ID1 but since - (empty) value in Table 2, I have skipped it from Expected table.

I need generated result as a Dataset

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 10, 2022, 2:49pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/2 "2022-11-10T14:49:54Z")

</div>

Where are you actually pulling this info from?

---

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [November 10, 2022, 2:50pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/3 "2022-11-10T14:50:21Z")

</div>

I will be pulling it from MSSQL.

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [November 10, 2022, 2:52pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/4 "2022-11-10T14:52:04Z")

</div>

And you want to put it into tables on perspective?

Or are you just creating a new table in MSSQL?

---

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [November 10, 2022, 2:52pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/5 "2022-11-10T14:52:58Z")

</div>

Ah yes, I want the output as dataset. that could be placed in Table in Perspective

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [November 10, 2022, 2:54pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/6 "2022-11-10T14:54:56Z")

</div>

Is there anything in table 2 to preserve row order?

---

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [November 10, 2022, 2:59pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/7 "2022-11-10T14:59:20Z")

</div>

Yes there is a column called StepName. I have updated snip in the post

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [November 10, 2022, 8:43pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/8 "2022-11-10T20:43:05Z")

</div>

Might be able to use a pivot?

---

<div class="post-metadata">

**Author:** ![Gaurav.Shitole](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/gaurav.shitole/32/72050_2.png) [@Gaurav.Shitole](https://forum.inductiveautomation.com/u/Gaurav.Shitole)\
**Post date:** [November 10, 2022, 9:16pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/9 "2022-11-10T21:16:02Z")

</div>

How..?

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [November 10, 2022, 9:32pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/10 "2022-11-10T21:32:08Z")

</div>

Stack overflow 🙂 I don't use pivots enough to offer quick help at the moment sorry

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [November 10, 2022, 10:26pm UTC](https://forum.inductiveautomation.com/t/advanced-sql-query-help/66353/11 "2022-11-10T22:26:44Z")

</div>

Use SQL, returning JSON, to build Table 1 and Table 2:

Table 1 data will be something like:

```auto
[
   {'id': 1, 'col1': 'AAA', 'col2':BBB},
   {'id': 2, 'col1': 'FFF', 'col2':GGG}
]

```

Table 2 will be something like:

```auto
[
   {'id': 1, 'col1': 111, 'col2': 222, 'StepName':1},
   {'id': 1, 'col1': 444, 'col2': None, 'StepName':2},
   {'id': 1, 'col1': None, 'col2': 888, 'StepName':3},
]

```

Then organize the data with a script like:

```auto
table_1 = [
   {'id': 1, 'col1': 'AAA', 'col2':'BBB'},
   {'id': 2, 'col1': 'FFF', 'col2':'GGG'}
]

table_2 = [
   {'id': 1, 'col1': 111, 'col2': 222, 'StepName':1},
   {'id': 1, 'col1': 444, 'col2': None, 'StepName':2},
   {'id': 1, 'col1': None, 'col2': 888, 'StepName':3},
]

result_value = []
for item in table_1:
	if item['id'] == 1:
		col_dict = item
		del col_dict['id']
		break

for item in table_2:
	if item['id'] == 1:
		result_value.append({col_dict_val: item[col_dict_key] for col_dict_key, col_dict_val in col_dict.items()})
		result_value[-1]['StepName'] = item['StepName']
		
print result_value

```