Say I have 3 list of data that I am using to build a new query. I need to take these list of data and return the values of those list where things were found.
So my question is this:
Is there a standard method of taking a list and using it as a column?
I will need to use multiple list as columns where one column is the “JOIN ON” or “WHERE IN” portion.
The results from my first query are used to build my 3 list.
Say I get back this data:
[[ID, TYPE, OTHER], [1, C, S], [2, C, O], [3, D, D], [4, D, H]]
Then convert that table/2D Array to the following Python List:
[1, 2, 3, 4] [C, C, D, D] [S, O, D, H]
Now I want to use those 2 list as columns in a select statement like this:
select [C, C, D, D] as TYPE # These 2 list are needed to return in the correct order
,[S, O, D, H] as OTHER # as it relates to [1, 2, 3, 4] in the WHERE.
,table.value
,table.color
From table
where table.value in [1, 2, 3, 4] # one list is used to deal with the where
table contains 2 columns:
VALUE COLOR 1 Red 2 Green 3 Blue 4 Black
Results should look like this:
TYPE OTHER VALUE COLOR C S 1 Red C O 2 Green D D 3 Blue D H 4 Black
Advertisement
Answer
Ok so here is the solution I have to go with.
Instead of managing the data on the client side I will create a new database on the SQL Server and then all the needed tables the tool will interact with.
Then I will have the program insert and delete rows in each table based on the user who is using the tool.
Getting the username is simple as:
import os print(os.getlogin())
As there will 99% of the time be less than 100 rows being added and deleted for any of the say 10 users at a time this will be efficient enough to handle the work.
This solutions is far more efficient then my current UNION method and will also allow each user to only see and work with data related to their login.
Seeing that each table will likely never exceed a total of 10,000 rows I don’t think this will be much of an issue even without having a primary key to work with.