I have a SQL Server table like this:

The title of the column shows that there are 2 projects (ProjectA, ProjectB) and a responsible for each one. The names of the colums will always be like that, so if a ProjectC would exist, 2 additional coumns with the names "ProjectC_Responsible" and "ProjectC_Result" would get added.

I want to get all IDs where "Simpson" has to do something, so the "Result" of his project is "open". So in my example I want to get:

I currently do not have an idea how to do this with a SQL statement. I'm using a MS Access frontend in the background so I may also solve it later with VBA, but I hope there is a direct solution with an SQL statement?

CodePudding user response：

As stated in the comments, you have a problem with your table design, this design is not scalable, and requires you to change the table structure and queries every time you add a new project.

You can use another table with a correct design and transfer the existing data to it with a simple query. consider the following:

create table NewTable (Part_ID int, Project_Title nvarchar(50),Project_Responsible nvarchar(50),
Project_Result nvarchar(50));

insert into NewTable 
Select Part_ID,'Project A',ProjectA_Responsible,ProjectA_Result From OldTable;
insert into NewTable 
Select Part_ID,'Project B',ProjectB_Responsible,ProjectB_Result From OldTable;

Repeat the insert into NewTable Select... From OldTable according to the number of projects you have.

Now you can simply query the new table as the following:

Select Part_ID, Project_Title,Project_Responsible,Project_Result From NewTable 
where Project_Responsible='Simpson' and Project_Result='open'

See a demo from here.

CodePudding user response：

select *
from [TableName]
where (ProjectA_Responsible = 'Simpson' and ProjectA_Result = 'open')
or (ProjectB_Responsible = 'Simpson' and ProjectB_Result = 'open')