SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table item
(RECORD_KEY bigint, NAME varchar(100));

insert into item values
(5935753887661641498,'COOKIE DOUGH IC'),
(5935753858146389769,'BLACK RASP IC'),
(5935753875199381267,'CHOC CHIP IC'),
(5935753885692612377,'COFFEE IC'),
(5935753956554460991,'STRAWBERRY IC'),
(5935753967705804613,'VANILLA IC'),
(5935753973608501064,'VEGAN OREO'),
(5935753877166837524,'CHOC CHOC CHIP IC'),
(3189656780758021441,'$2 sundae sale');
9 rows affected

create table modifier_list
(RECORD_KEY bigint, NAME varchar(100));

insert into modifier_list values
(5992426424268357750,'ICE CREAM FLAVORS');
1 rows affected

create table modifier_group
(RECORD_KEY	bigint, MODIFIER_LIST_RECORD_KEY bigint, NAME varchar(100));

insert into modifier_group values
(5992441193994846327,5992426424268357750,'CHOOSE ICE CREAM');
1 rows affected

create table item_modifier
(RECORD_KEY bigint,	ITEM_RECORD_KEY	bigint, MODIFIER_LIST_RECORD_KEY bigint);

insert into item_modifier values
(3189656801236120901, 3189656780758021441, 5992426424268357750);
1 rows affected

create table modifier
(RECORD_KEY bigint, 
 MODIFIER_GROUP_RECORD_KEY bigint,
 NAME varchar(100),
 ITEM_RECORD_KEY bigint,
 UPCHARGE_EXPRESSION money);

insert into modifier values
(5992479917748650109,5992441193994846327,NULL,5935753858146389769,NULL),
(5992548895904366727,5992441193994846327,NULL,5935753875199381267,NULL),
(5992553113958285448,5992441193994846327,NULL,5935753877166837524,NULL),
(5992590751191728269,5992441193994846327,NULL,5935753885692612377,NULL),
(5992596587928879246,5992441193994846327,NULL,5935753887661641498,NULL),
(5992752244786331827,5992441193994846327,NULL,5935753956554460991,NULL),
(5992774327866622137,5992441193994846327,NULL,5935753967705804613,NULL),
(5992784415143821500,5992441193994846327,NULL,5935753973608501064,NULL),
(5993568288307740930,5992441193994846327,'CHOOSE LATER',NULL,NULL);
9 rows affected

SELECT
I.NAME AS 'ITEM NAME'
,ML.NAME AS 'MODIFIER LIST'
,MG.NAME AS 'MODIFIER GROUP'
,COALESCE(M.NAME,IR.name)  AS 'MODIFIER'
,FORMAT(CAST(M.UPCHARGE_EXPRESSION AS numeric), 'c', 'en-us') AS 'UPCHARGE'
FROM ITEM i
LEFT JOIN ITEM_MODIFIER IM ON IM.ITEM_RECORD_KEY = I.RECORD_KEY
LEFT JOIN MODIFIER_LIST ML ON ML.RECORD_KEY = IM.MODIFIER_LIST_RECORD_KEY
LEFT JOIN MODIFIER_GROUP MG ON MG.MODIFIER_LIST_RECORD_KEY = ML.RECORD_KEY
LEFT JOIN MODIFIER M ON M.MODIFIER_GROUP_RECORD_KEY = MG.RECORD_KEY
LEFT JOIN ITEM IR ON IR.RECORD_KEY = M.ITEM_RECORD_KEY
WHERE ML.NAME IS NOT NULL
GROUP BY I.NAME, ML.NAME, MG.NAME, M.NAME, IR.NAME, M.UPCHARGE_EXPRESSION
ORDER BY I.NAME, ML.NAME, MG.NAME, M.NAME


ITEM NAME
MODIFIER LIST
MODIFIER GROUP
MODIFIER
UPCHARGE


$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
BLACK RASP IC
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
CHOC CHIP IC
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
CHOC CHOC CHIP IC
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
COFFEE IC
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
COOKIE DOUGH IC
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
STRAWBERRY IC
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
VANILLA IC
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
VEGAN OREO
null

$2 sundae sale
ICE CREAM FLAVORS
CHOOSE ICE CREAM
CHOOSE LATER
null

ITEM NAME	MODIFIER LIST	MODIFIER GROUP	MODIFIER	UPCHARGE
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	BLACK RASP IC	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	CHOC CHIP IC	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	CHOC CHOC CHIP IC	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	COFFEE IC	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	COOKIE DOUGH IC	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	STRAWBERRY IC	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	VANILLA IC	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	VEGAN OREO	null
$2 sundae sale	ICE CREAM FLAVORS	CHOOSE ICE CREAM	CHOOSE LATER	null