SQL Server Pivot with Inner Join

Question

I have two tables

Table 1:

    -----------------------------------------------
    |  iFirstID  |  fkSomeID  |  cText  | bStatus |
    |     1      |     59     |  'blah' |    1    |
    |     2      |     100    |  'text' |    0    |
    -----------------------------------------------

Table 2:

    -------------------------------------------------------------------------
    |  iSecondID  |   fkFirstID  |   fkOtherID  |  cSomeText  |  bSomeBool  |
    |      1      |       1      |       1      |    "text"   |      1      |
    |      2      |       1      |       2      | "more text" |      0      |
    |      3      |       1      |       3      | "more text" |      0      |
    |      4      |       2      |       1      |   "blah"    |      1      |
    |      5      |       2      |       2      |   "test"    |      1      |
    |      6      |       2      |       3      |   "data"    |      0      |
    -------------------------------------------------------------------------

I'd like to write a query which will return the following dataset based on the above

Result:

    -----------------------------------------------------------------------------------------------------
    |  fkFirstID  |  fkSomeID  |  cText  |  bSomeBool[1]  |  bSomeBool[2]  |  bSomeBool[3]  |  bStatus  |   
    |      1      |     59     |  'blah' |      1         |       0        |        0       |     0     |
    |      2      |    100     |  'text' |      1         |       1        |        0       |     0     |
    -----------------------------------------------------------------------------------------------------

As you can see in the above, bSomeBool will be reported for every row in table 2 with the same fkFirstID.

I'm not familiar with pivots at all, but am pretty sure this is what I'll need to use to accomplish what I'm looking for. I've looked at a few suggestions online (like this one: SQL server join tables and pivot) but can't wrap my head around it, as most differ a little from what I'm trying to accomplish.

There is a third table not included which is where fkOtherID comes into play. We can assume this table to have two columns: iOtherID and cColumnName

In Table 2, we will never have a duplicate fkOtherID for the same fkFirstID. So for each fkOtherID value, we would go to table 3, and set the column name as cColumnName. Not sure if this will affect the final solution too much.

I've tried using an inner join, but the result was that it would only look at the first row of Table 2.

I tried using Group By, but it kept returning errors for my other columns as they weren't a part of an aggregate function, but even still I don't think this would be what I want as I think it'd return two rows, one with a 1, and the other with a 0 for the bSomeBool column.

Answer 1

As I said in my comment, it's more of a dynamic SELECT problem than a PIVOT problem. Here's how I would do it with dynamic SQL.

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

fiddle