如何为表中的每列指定1或0？

I have a table called Person with 15 columns. Depending on the EventId, certain columns will be used and certain columns will not be used. For example, an EventId of 5 will use the columns Name, Address, and Phone. Meanwhile an EventId of 10 will use the columns Name and Email.

我有一个名为Person的表,有15列。根据EventId,将使用某些列,并且不使用某些列。例如,EventId为5将使用名称,地址和电话列。同时,EventId为10将使用名称和电子邮件列。

I think I have to make a table with the EventId and somehow put a 1 or 0 in to know which columns that particular event should have. However I do not know how to tie all of this together and make it work.

我想我必须用EventId创建一个表,并以某种方式放入1或0来知道特定事件应该具有哪些列。但是我不知道如何将所有这些结合在一起并使其工作。

I am using SQL Server.

我正在使用SQL Server。

1 个解决方案

#1

create table  #test (EventID varchar(max), Name varchar(max), [Address] varchar(max), [Phone] varchar(max))
insert into #test 
values ('1','Abc','gabanes adfga','1521623612'),
       ('3','adz','gabwega abae' ,'1521623612'),
       ('5','qqb','asdhahn ansxeh','1521623612'),
       ('5','qas','smystrj sthshs','1521623612'),
       ('2','qar','sthtr sthsht ','1521623612'),
       ('1','qha','msgnsn abawe ','1521623612')

; with cte as 
(
select *, case when EventID = 5 then 1 else 0 end as Flag from #test 
) 
select Name, [Address], Phone  from cte
where flag = 1

;with cte1
 as ( select *, case when EventID = 5 then 1 else 0 end as Flag from #test )
 select Name, Phone from cte1 
 where flag != 1 

I have named the columns randomly and just put garbage data into the fields. However, hope this helps achieve what you are trying to do.

我已经随机命名了列,只是将垃圾数据放入字段中。但是,希望这有助于实现您的目标。