SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dataOne (
  iFirstID int
  , fkSomeID int
  , cText nvarchar(50)
  , bStatus int
);

INSERT INTO dataOne (iFirstID, fkSomeID, cText, bStatus)
VALUES 
  (1,59,'blah',1)
  , (2,100,'text',0)
;

CREATE TABLE dataTwo (
  iSecondID int
  , fkFirstID int
  , fkOtherID int
  , cSomeText nvarchar(50)
  , bSomeBool int
);

INSERT INTO dataTwo (iSecondID, fkFirstID, fkOtherID, cSomeText, bSomeBool)
VALUES
  (1,1,1,'text',1)
  , (2,1,1,'more text',0)
  , (3,1,1,'more text',0)
  , (4,2,1,'blah',1)
  , (5,2,1,'test',1)
  , (6,2,1,'data',0)
;

--Get the maximum number of events so we know how many columns to create.
DECLARE @maxBools int;
SET @maxBools = (
  SELECT MAX(d2.row_num)
  FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY fkFirstID ORDER BY fkOtherID) as row_num
    FROM dataTwo
  ) as d2 );

--Create a variable to hold the dynamic query.
DECLARE @sqlText nvarchar(max);

--Create the first part of the SELECT statment with place holders
--where we can insert columns ({{0}}) and JOINS ({{1}})
SET @sqlText = '
  WITH prelim as (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY fkFirstID ORDER BY fkOtherID) as row_num
    FROM dataTwo
  )
  SELECT DISTINCT d1.iFirstID, d1.fkSomeID, d1.cText, d1.bStatus
    {{0}}
  FROM dataOne as d1
    {{1}}
  ';

--Setup loop variables.
DECLARE @loopCount int = 1;
DECLARE @loopCText nvarchar(50);

WHILE (@loopCount <= @maxBools)
BEGIN
  --Get a string for the loop count... pad it with zeros if multi-digits.
  SET @loopCText = RIGHT(
    '0000000000' + CAST(@loopCount as nvarchar(10))
    , LEN(CAST(@maxBools as nvarchar(50)))
  );

--Add a SELECT entry for this loop instance.
  SET @sqlText = REPLACE(@sqlText, '{{0}}', '
    , d2' + @loopCText + '.bSomeBool as bSomeBool' + @loopCText + '
    {{0}}
  ');

--Add a JOIN entry for this loop instance.
  SET @sqlText = REPLACE(@sqlText, '{{1}}', '
    LEFT OUTER JOIN prelim as d2' + @loopCText + '
      ON d2' + @loopCText + '.fkFirstID = d1.iFirstID
      AND d2' + @loopCText + '.row_num = ' + CAST(@loopCount as nvarchar(10)) + '
    {{1}}
  ');

--SELECT @sqlText;  --For debugging
  SET @loopCount = @loopCount + 1;
END

--Post Loop Cleanup... remove template place holders.
SET @sqlText = REPLACE(@sqlText, '{{0}}', '');
SET @sqlText = REPLACE(@sqlText, '{{1}}', '');

EXEC(@sqlText);

iFirstID	fkSomeID	cText	bStatus	bSomeBool1	bSomeBool2	bSomeBool3
1	59	blah	1	1	0	0
2	100	text	0	1	1	0