SELECT
	s.[supplier_number]
	, s.[supplier_name]
	, sl.[supplier_location]
FROM
	supplier AS s

	OUTER APPLY ( 
		SELECT TOP 4
			[supplier_location]
		FROM
			supplier_locations AS loc
		WHERE
			loc.[supplier_num] = s.[supplier_num]
		ORDER BY
			loc.[supplier_location]
		)
		AS sl

DECLARE @supplier TABLE (
	[supplier_number] INT PRIMARY KEY
	, [supplier_name] VARCHAR(255)
	)

DECLARE @supplierLocation TABLE (
	[supplierLocationID] INT IDENTITY (1,1) PRIMARY KEY
	, [supplier_number] INT
	, [supplier_location] VARCHAR(MAX)
	)

INSERT @supplier ([supplier_number], [supplier_name])
      SELECT 1, 'ACME'
UNION SELECT 2, 'UNIVERSAL'
UNION SELECT 3, 'FOO INDUSTRIES'

INSERT @supplierLocation ([supplier_number], [supplier_location])
      SELECT 1, 'COLORADO'
UNION SELECT 1, 'NORTH SPRINGS'
UNION SELECT 1, 'EASTERHOUSE'
UNION SELECT 1, 'MUSSLEBURGH'
UNION SELECT 1, 'KENTISH TOWN'
UNION SELECT 1, 'GRANTHON'
UNION SELECT 2, 'WEST LOTHIAN'
UNION SELECT 2, 'LENZIE'

SELECT
	[supplier_number]
	, [supplier_name]
	, MAX(CASE WHEN [rowPos] = 1 THEN [supplier_location] ELSE '' END) AS [Supplier_location1]
	, MAX(CASE WHEN [rowPos] = 2 THEN [supplier_location] ELSE '' END) AS [Supplier_location2]
	, MAX(CASE WHEN [rowPos] = 3 THEN [supplier_location] ELSE '' END) AS [Supplier_location3]
	, MAX(CASE WHEN [rowPos] = 4 THEN [supplier_location] ELSE '' END) AS [Supplier_location4]
FROM
	(
	SELECT
		s.[supplier_number] AS [supplier_number]
		, s.[supplier_name] AS [supplier_name]
		, sl.[supplier_location] AS [supplier_location]
		, ROW_NUMBER() OVER (PARTITION BY s.[supplier_number] ORDER BY sl.[supplier_location]) AS [rowPos]
	FROM
		@supplier AS s
		LEFT JOIN @supplierLocation AS sl ON sl.[supplier_number] = s.[supplier_number]
	)
	AS prePivot
WHERE
	prePivot.[rowPos] <= 4
GROUP BY
	[supplier_number]
	, [supplier_name]