как разбить два столбца на две строки по id

Рейтинг: 0Ответов: 2Опубликовано: 27.03.2023
select
case_id,
a.id_police,
c.sign_date,
d.sign_date as sign_date2,
d.id_condition,
e.id_condition as id_condition2
--occurr_date,
--row_number() over (partition by a.id_police  order by d.sign_date desc) as nums, 
--dateadd("yyyy", 0, getdate()) as ourDate
from loss.tbl_Insurance_Cases a 
join loss.tbl_Occurrences b on a.occurr_id = b.occurr_id
join tbl_policies c on a.id_police = c.id_police
left join tbl_Agreements d on a.id_police  = d.id_police
join tbl_InsuranceConditions e on c.id_conditions = e.id_condition


  [1]: https://i.sstatic.net/OT0rw.png
sql
Источник: Stack Overflow на русском

Ответы

▲ 0

Используйте UNION. Один раз берите одну колонку, второй - другую.

select
case_id,
a.id_police,
c.sign_date,
d.sign_date as sign_date2,
d.id_condition,
from loss.tbl_Insurance_Cases a 
join loss.tbl_Occurrences b on a.occurr_id = b.occurr_id
join tbl_policies c on a.id_police = c.id_police
left join tbl_Agreements d on a.id_police  = d.id_police
union
select
case_id,
a.id_police,
c.sign_date,
d.sign_date as sign_date2,
e.id_condition,
from loss.tbl_Insurance_Cases a 
join loss.tbl_Occurrences b on a.occurr_id = b.occurr_id
join tbl_policies c on a.id_police = c.id_police
left join tbl_Agreements d on a.id_police  = d.id_police
join tbl_InsuranceConditions e on c.id_conditions = e.id_condition
▲ 0

Уточните СУБД, которую используете. А так по классике можно примерно так:

select
  case_id,
  a.id_police,
  c.sign_date,
  d.sign_date as sign_date2,
  case 
    when Tp.tp = 1 
    then d.id_condition
    else e.id_condition
  end as id_condition
from loss.tbl_Insurance_Cases a 
  join loss.tbl_Occurrences b on a.occurr_id = b.occurr_id
  join tbl_policies c on a.id_police = c.id_police
  left join tbl_Agreements d on a.id_police  = d.id_police
  join tbl_InsuranceConditions e on c.id_conditions = e.id_condition
  cross join (select 1 as tp union all select 2)as Tp(tp)

Суть в том что соединяем со сгенерированной табличкой с двумя строками (1),(2) - а потом в зависимости от значения выбираем нужный Ид