122, Sql Server - rextester

Language: Layout:

CREATE TABLE HORIZONTAL (
id INT NOT NULL  PRIMARY KEY,
NAME VARCHAR(40) NOT NULL,
VALUE INT NOT NULL
);

INSERT INTO HORIZONTAL(id, name, value) VALUES(1, 'jersey', 22);
INSERT INTO HORIZONTAL(id, name, value) VALUES(2, 'newYork', 33);
INSERT INTO HORIZONTAL(id, name, value) VALUES(3, 'texas', 44);

CREATE TABLE VERTICAL (
id INT NOT NULL  PRIMARY KEY,
PRODNAME VARCHAR(40) NOT NULL,
);

INSERT INTO VERTICAL(id, prodname) VALUES(1, 'apple');
INSERT INTO VERTICAL(id, prodname) VALUES(2, 'orange');
INSERT INTO VERTICAL(id, prodname) VALUES(3, 'kiwi');

DECLARE @cols nvarchar(max) = STUFF(
							  (SELECT DISTINCT ',[' + h.name + ']' FROM HORIZONTAL h FOR XML PATH (''))
							 ,1,1,'')
--PRINT @cols

DECLARE @query nvarchar(max) = N'SELECT id, prodname, ' + @cols + char(13) + 
						  N' FROM 
						  (   select v.*, h.name, h.value 
							 from VERTICAL v
							 CROSS JOIN dbo.HORIZONTAL h    
						  ) src
						  PIVOT
						  (
							 MIN(value) FOR name IN ('+ @cols +')
						  ) pvt'

--PRINT @query
EXEC (@query)

DROP TABLE HORIZONTAL
DROP TABLE VERTICAL

View schema
Execution time: 0,02 sec, rows selected: 5, rows affected: 6, absolute service time: 0,17 sec