SQL Server T-SQL Rows to Columns

Question

I am not sure If I'm doing it correctly but my requirement was to create a view display rows into columns using TSQL. Column number is fixed. Rows will never exceed the number of columns.

Limit in Col2 is 3. No limit in Col 1.

Currently my SQL is using OFFSET AND FETCH but it seems its always returning a total of 1 row.

 SELECT Col1, Col2 FROM Table2 WHERE Col1 IN (SELECT Col FROM Table1) ORDER BY Col2 ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

Table 1
A
B
C
D

Table 2 Col 1	Table 2 Col 2
A	A1
A	A2
A	A3
B	B1

Output Col 1	Output Col 1	Output Col 2	Output Col 3
A	A1	A2	A3
B	B1	NULL	NULL
C	NULL	NULL	NULL
D	NULL	NULL	NULL

Thanks you.

Answer 1

I would, personally, use conditional aggregation for this, which would look a little like this:

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];