SQL Server 2017

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

CREATE TABLE [PRODUCT](
    [PRODUCTID] [INT] NOT NULL,
    [PRODUCTMODELID] [INT],
    [NAME] varchar(50) NOT NULL,
    [PRODUCTNUMBER] [NVARCHAR](25) NOT NULL,
)

CREATE TABLE [PRODUCTMODEL](
    [PRODUCTMODELID] [INT] NOT NULL,
    [NAME] [NVARCHAR](25) NOT NULL
)

INSERT INTO PRODUCTMODEL (PRODUCTMODELID, NAME)
VALUES (11, 'LONG-SLEEVE LOGO JERSEY')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (713, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-S')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (714, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-M')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (715, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-L')

INSERT INTO PRODUCT (PRODUCTID, PRODUCTMODELID, NAME, PRODUCTNUMBER)
VALUES (716, 11, 'LONG-SLEEVE LOGO JERSEY', 'LJ-0192-X')

;WITH CTE AS (
	SELECT p.PRODUCTMODELID PRODUCTMODELID,
	       p.NAME pName,
		   pm.Name pmName,
		   PRODUCTID,
		   PRODUCTNUMBER
	FROM PRODUCT p JOIN PRODUCTMODEL pm 
	ON pm.PRODUCTMODELID =p.PRODUCTMODELID
)
SELECT v.*
FROM CTE CROSS APPLY (
	VALUES 
	('PRODUCT',pmName,PRODUCTID,PRODUCTNUMBER),
	('MODEL',pName,PRODUCTMODELID,'')
) v (col1,col2,col3,col4)

col1	col2	col3	col4
PRODUCT	LONG-SLEEVE LOGO JERSEY	713	LJ-0192-S
MODEL	LONG-SLEEVE LOGO JERSEY	11
PRODUCT	LONG-SLEEVE LOGO JERSEY	714	LJ-0192-M
MODEL	LONG-SLEEVE LOGO JERSEY	11
PRODUCT	LONG-SLEEVE LOGO JERSEY	715	LJ-0192-L
MODEL	LONG-SLEEVE LOGO JERSEY	11
PRODUCT	LONG-SLEEVE LOGO JERSEY	716	LJ-0192-X
MODEL	LONG-SLEEVE LOGO JERSEY	11