Query for making multiple indefinite rows into one row with multiple columns

I have two tables

And the second one

The second table has more columns that I need to include, but I'm only including two (Code and Date1) for the sake of brevity.

What I need is to unite everything based on the ID of the first table. So it would look something like

In these examples one ID repeats up to two times in the second table, but the second table can have an indefinite amount of records tied to an ID from the first table. Meaning it might be Code-10, or Code-20, or maybe more or less. I need to do this in a pretty big query for a report I'm doing, so these are not the only fields that will be in the final result, but for this data specifically I only use two tables that have a very similar structure to the one I'm describing here. Any help will be appreciated.

To build on T N's code, this puppy builds a "dynamic" 30 column wide pivot.

DECLARE @SQL NVARCHAR(MAX)

SET	@SQL = N'
SELECT
    A.ID
    [COLUMNS]
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID2) AS RowNum
    FROM TableA
) A
JOIN TableB B
    ON B.ID2 = A.ID2
GROUP BY A.ID
ORDER BY A.ID'

SELECT	@SQL = REPLACE(@SQL, '[COLUMNS]', (
		SELECT	CONCAT(N'
		,	MAX(CASE WHEN A.RowNum = ',  x.sort, ' THEN B.ID2 END) AS [ID2-', x.sort, N']
		,	MAX(CASE WHEN A.RowNum = ', x.sort, ' THEN B.Code END) AS [Code-', x.sort, N']
		,	MAX(CASE WHEN A.RowNum = ', x.sort, ' THEN B.Date END) AS [Date-', x.sort, N']')
		FROM	(
			SELECT	TOP 30 row_number() OVER(ORDER BY (SELECT	NULL)) AS sort
			FROM	sys.objects so
			) x
		FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'))
SELECT	@SQL


EXEC(@SQL)

You can accomplish this by using a combination of a windowed ROW_NUMBER() number function and conditional aggregation.

First, ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) is used to assign sequences number to each row with the same ID. The results are then be joined with your second table and a GROUP BY ID is applied. Finally, conditional aggregation functions of the form MAX(CASE WHEN ... THEN .. END) can be used to select the proper value for each column, with specific row numbers assigned to specific columns.

Something like:

SELECT
    A.ID,
    MAX(CASE WHEN A.RowNum = 1 THEN B.ID2 END) AS [ID2-1],
    MAX(CASE WHEN A.RowNum = 1 THEN B.Code END) AS [Code-1],
    MAX(CASE WHEN A.RowNum = 1 THEN B.Date END) AS [Date-1],
    MAX(CASE WHEN A.RowNum = 2 THEN B.ID2 END) AS [ID2-2],
    MAX(CASE WHEN A.RowNum = 2 THEN B.Code END) AS [Code-2],
    MAX(CASE WHEN A.RowNum = 2 THEN B.Date END) AS [Date-2]
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID2) AS RowNum
    FROM TableA
) A
JOIN TableB B
    ON B.ID2 = A.ID2
GROUP BY A.ID
ORDER BY A.ID

Results:

See this db<>fiddle.

If you need to support an arbitrary number of records per ID instead of just two, you will then need to dive into the world of dynamic SQL.