Блог

 CREATE TABLE dbo.children_bd (
    birthday_id int,
    child_birthday_id int,
    notification_parents date,
    notification_grandparents date,
    invitation_parents_id int,
    invitation_grandparents_id int
);

INSERT INTO dbo.children_bd
VALUES (1,9, '02-01-2021','07-01-2021', 4, 5);
 

Вот мой код:

 select birthday_id, child_birthday_id, tm, rm
from (
    select p.birthday_id, p.child_birthday_id, p.notification_parents,
p.notification_grandparents, p.invitation_parents_id, p.invitation_grandparents_id
    from dbo.children_bd p
) t
UNPIVOT
(tm for id in (invitation_parents_id, invitation_grandparents_id)) pvt1
UNPIVOT
(rm for rid in (notification_parents, notification_grandparents)) pvt2
 

Я получаю это:

  ------------ ------------------- ----- ------------- 
|birthday_id | child_birthday_id | tm  |  rm         |
 ------------ ------------------  ----- ------------- 
|  1         |        9          |  4  | 2021-02-01  |
|  1         |        9          |  4  | 2021-07-01  |
|  1         |        9          |  5  | 2021-02-01  |
|  1         |        9          |  5  | 2021-07-01  |
 ------- ---------- ------------- ----- --------------
 

но я хотел бы получить это:

  ------------ ------------------- ----- ------------- 
|birthday_id | child_birthday_id | tm  |  rm         |
 ------------ ------------------  ----- ------------- 
|  1         |        9          |  4  | 2021-02-01  |
|  1         |        9          |  5  | 2021-07-01  |
 ------- ---------- ------------- ----- --------------
 

Если вы хотите отключить несколько значений, вы можете использовать CROSS APPLY вместе с конструктором табличных значений:

 SELECT c.birthday_id,
       c.child_birthday_id,
       upvt.Type,
       upvt.NotificationDate,
       upvt.InvitationID
FROM   dbo.children_bd AS c
       CROSS APPLY 
       (VALUES 
           ('Parents', c.notification_parents, c.invitation_parents_id),
           ('Grandparents', c.notification_grandparents, c.invitation_grandparents_id)
        ) AS upvt (Type, NotificationDate, InvitationID);
 

Пример в db<>fiddle