在T-SQL中聚合一列中的列(或添加新行)

Question

在我的SQL Server (T-SQL)查询中，我想要2列作为结果:eventsID和T_E_Discriminatorio_ID。

这是我的查询:

select 
e.id eventsID,
case 
when DisabilityID is not null 
then (select T_E_Discriminatorio_ID 
from t_e_discriminatorio ted, Disability d 
where ted.Dettaglio_T_E_Discriminatorio = d.Name
and DisabilityID = d.Id)
end T_E_Discriminatorio_ID,
case 
when EthnicityId is not null 
then (select T_E_Discriminatorio_ID 
from t_e_discriminatorio ted, Ethnicity e 
where ted.Dettaglio_T_E_Discriminatorio = e.Name
and EthnicityId = e.Id)
end T_E_Discriminatorio_ID
from
events e 
inner join 
DiscriminatoryAct d on e.id = d.EventId;

结果是:

eventsID T_E_Discriminatorio_ID T_E_Discriminatorio_ID
62            8                        NULL
63            8                        7    
64            NULL                     7        
65            7                        6    

我想要聚合T_E_Discriminatorio_ID for有:

62 8
63 8
63 7
64 7
65 7
65 6

当有2个T_E_Discriminatorio_ID时，插入一个具有相同events_id的新行

模式如下:

t_e_discriminatorio:
T_E_Discriminatorio_ID 
T_E_Discriminatorio 
Dettaglio_T_E_Discriminatorio <---> fk to Name on table (Disability or Ethnicity or Gender or Religion or SexualOrientation) table 
DiscriminatoryAct:
Id
CrimeId
EventId   <---> fk to id on events table
Note
OSCECategoryId
DiscriminatoryArea
DisabilityId  <---> fk to id on Disability table
EthnicityId <---> fk to id on Ethnicity table
GenderId <---> fk to id on Gender table
ReligionId <---> fk to id on Religion table
SexualOrientationId <---> fk to id on SexualOrientation table
Disability (like Ethnicity or Gender or Religion or SexualOrientation)
Id <--> fk to DisabilityId on DiscriminatoryAct
Name <--> fk to Dettaglio_T_E_Discriminatorio on t_e_discriminatorio

我该怎么做呢?

谢谢关于

Answer 1

您可以使用UNION将两个case语句组合在一起。根据您向我们展示的模式，下面的操作应该会给您所需的结果:

select * from (
select
disc_a.event_ID as Event_ID,
ev.T_E_Discriminatorio_ID as T_E_Discriminatorio_ID 
from DiscriminatoryAct disc_a
inner join Disability d on disc_a.DisabilityId = d.ID
inner join Events ev on ev.event_ID = disc_a.event_ID
UNION
select
disc_a.event_ID as Event_ID,
ev.T_E_Discriminatorio_ID as T_E_Discriminatorio_ID 
from DiscriminatoryAct disc_a
inner join Ethnicity e on disc_a.EthnicityId = e.Id
inner join Events ev on ev.event_ID = disc_a.event_ID
) a
order by a.Event_ID