就我个人而言，我会使用条件聚合，它看起来有点像这样：

SELECT T1.[Table 1] AS [Output Col 1],
       MAX(CASE [Table 2 Col 2] WHEN T1.[Table 1] + '1' THEN [Table 2 Col 2] END) AS [Output Col 1],
       MAX(CASE [Table 2 Col 2] WHEN T1.[Table 1] + '2' THEN [Table 2 Col 2] END) AS [Output Col 2],
       MAX(CASE [Table 2 Col 2] WHEN T1.[Table 1] + '3' THEN [Table 2 Col 2] END) AS [Output Col 3]
FROM dbo.YourFirstTable T1
     LEFT JOIN dbo.YourSecondTable T2 ON T1.[Table 1] = T2.[Table 2 Col 1]
GROUP BY T1.[Table 1];

作为对Larnu回答的回应，[添加了表2第3列]，可用于排序。抱歉，不知道它是否存在。
在使用了Larnu的答案后，我得到了下面的SQL，我认为它足够满足我的要求。非常感谢Larnu的帮助。

SELECT 
T1.[Table 1] AS [Output Col 1],
(SELECT [Table 2 Col 2] FROM  dbo.YourSecondTable WHERE  dbo.YourSecondTable.[Table 2 Col 1]=T1.[Table 1] ORDER BY dbo.YourSecondTable.[Table 2 Col 3] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Output Col 1],     
(SELECT [Table 2 Col 2] FROM  dbo.YourSecondTable WHERE  dbo.YourSecondTable.[Table 2 Col 1]=T1.[Table 1] ORDER BY dbo.YourSecondTable.[Table 2 Col 3] OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS [Output Col 2],     
(SELECT [Table 2 Col 2] FROM  dbo.YourSecondTable WHERE  dbo.YourSecondTable.[Table 2 Col 1]=T1.[Table 1] ORDER BY dbo.YourSecondTable.[Table 2 Col 3] OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY) AS [Output Col 3]  
FROM dbo.YourFirstTable T1
LEFT JOIN dbo.YourSecondTable T2 ON T1.[Table 1] = T2.[Table 2 Col 1]
GROUP BY T1.[Table 1];

由于列数未知（我认为SQL Server的列数限制为1000），我将使用动态SQL构建一个动态“透视”，如下所示：

DROP TABLE IF EXISTS table_1
DROP TABLE IF EXISTS table_2
CREATE TABLE table_1([Col 1] VARCHAR(2) )
CREATE TABLE table_2([Col 1] VARCHAR(2) , [col 2] VARCHAR(2))

TRUNCATE TABLE Table_1
TRUNCATE TABLE Table_2
INSERT INTO TABLE_1([col 1])  VALUES ('A'),('B'),('C'),('D')
INSERT INTO TABLE_2([col 1], [col 2])  
VALUES ('A','A1'),('A','A2'),('A','A3'),('B','B1')
DROP TABLE IF EXISTS #Dict

;WITH cte1 AS (
    SELECT DISTINCT [col 1] , [col 2]
    FROM table_2
), cte2 AS (
SELECT [col 1], [col 2], ROW_NUMBER() OVER (PARTITION BY [Col 1] ORDER BY [Col 1],[col 2]) ColId
FROM cte1
)
SELECT DISTINCT *
INTO #Dict
FROM cte2
WHERE ColId < 999
--select * from #Dict

DROP TABLE IF EXISTS #Pivot 
DECLARE @SQLCmdCreate NVARCHAR(max) = 'CREATE TABLE #Pivot ([Table_1 Col 1] VARCHAR(2), '
DECLARE @SQLCmdInsert NVARCHAR(max) = 'INSERT INTO #Pivot ( [Table_1 Col 1], '
DECLARE @SQLCmdSelect NVARCHAR(max) = 'SELECT Table_1.[Col 1], '
DECLARE @SQLCmd       NVARCHAR(max) 
DECLARE @q NCHAR(1) = ''''
DECLARE @crlf NCHAR(2) = CHAR(13)+ CHAR(10)
SELECT @SQLCmdCreate += STRING_AGG(CONCAT('[Output ', ColId, '] VARCHAR(2)'), ',')
     , @SQLCmdInsert += STRING_AGG(CONCAT('[Output ', ColId, ']'), ',')
     , @SQLCmdSelect += STRING_AGG(CONCAT(@crlf, 'MIN(CASE WHEN [ColId] = ', [ColId], ' THEN Dict.[Col 2] END) '), ',') 
FROM (
    SELECT DISTINCT TOP 100 PERCENT ColId FROM #Dict ORDER BY ColId
) dict

SELECT @SQLCmdCreate+= ')'
   , @SQLCmdInsert += ')'
   , @SQLCmdSelect += @crlf + 'FROM Table_1 '
                    + 'left join #Dict dict ' 
                    + '    on  Dict.[Col 1] = Table_1.[Col 1]' 
                    + @crlf + 'GROUP BY table_1.[Col 1]'

SELECT @SQLCmd = @SQLCmdCreate + @crlf  + @SQLCmdInsert +  @crlf + @SQLCmdSelect + @crlf + 'SELECT * FROM #Pivot ORDER BY [Table_1 Col 1]'
--print @SQLCmd
EXEC (@SQLCmd)