Advanced SQL Query Help

Hello people,

I want to achieve something like below snip:


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

Where are you actually pulling this info from?

I will be pulling it from MSSQL.

And you want to put it into tables on perspective?

Or are you just creating a new table in MSSQL?

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

Is there anything in table 2 to preserve row order?

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

1 Like

Might be able to use a pivot?


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

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

Table 1 data will be something like:

   {'id': 1, 'col1': 'AAA', 'col2':BBB},
   {'id': 2, 'col1': 'FFF', 'col2':GGG}

Table 2 will be something like:

   {'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:

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']

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