SQL Server: Combine multiple rows into one with no overlap but new columns

Question

I have a Table "Details" like this:

    ID         |        BookID         |        Genre
    _____________________________________________________
    1          |         12            |         AB
    1          |         05            |         AC
    2          |         12            |         AB
    2          |         08            |         BB

Is there any way to combine the rows in this format:

    ID  | BookID1 | Genre1  | BookID2 | Genre2
    _____________________________________________________
    1   | 12      | AB      | 05      |   AC
    2   | 12      | AB      | 08      |   BB

I need to do it in such a format to add the respective columns to a stored procedure.

I generally would love to include some code or attempts but I'm clueless at this moment.

Not a Duplicate of the below: T-SQL dynamic pivot

1st difference im not using tsql

2nd difference is the structure of the table which can't be applied to mine.

My values are not going to be column headers instead they are gonna generate new columns such as :

    ID  | BookID1 | Genre1  | BookID2 | Genre2  | BookID3 | Genre3  | BookID4 | Genre4
    1   | 05      | Horror  | 12      | Horror  | 03      | Drama   |         | 
    2   | 05      | Horror  | 15      | Advent  | 03      | Drama   | 112     | RomCom

3rd Im not able to grasp the structure since I have zero knowledge of pivot tables

Answer 1

Try a dynamic sql query

CREATE TABLE #BookGenreMapping
(
   Id int,
   BookId int,
   Genre varchar(20)
)

INSERT INTO #BookGenreMapping VALUES (1,12,'AB'),(1,05,'AC'),(2,12,'AB'),(2,08,'BC'),(2,09,'BCD')
DECLARE @HeaderAll nvarchar(max)
DECLARE @ColumnPivotBookId nvarchar(max)
DECLARE @ColumnPivotGenre nvarchar(max)

;WITH temp AS
(
   SELECT *, 
   CONCAT('BookID' ,row_number() OVER(PARTITION BY bgm.Id ORDER BY bgm.BookId)) AS BookGroupId,
   CONCAT('BookGroupGenre' ,row_number() OVER(PARTITION BY bgm.Id ORDER BY bgm.BookId)) AS BookGroupGenre
   FROM #BookGenreMapping bgm
),
temp1 AS 
(
   SELECT DISTINCT t.BookGroupId, t.BookGroupGenre FROM temp t
)
SELECT @HeaderAll = STUFF((SELECT CONCAT(',',t.BookGroupId, ',', t.BookGroupGenre) FROM temp1 t FOR XML PATH('')), 1,1,''),
     @ColumnPivotBookId =  STUFF((SELECT CONCAT(',',t.BookGroupId) FROM temp1 t FOR XML PATH('')), 1,1,''),
     @ColumnPivotGenre =  STUFF((SELECT CONCAT(',', t.BookGroupGenre) FROM temp1 t FOR XML PATH('')), 1,1,'')

--SELECT @HeaderAll, @ColumnPivotBookId, @ColumnPivotGenre


DECLARE @query nvarchar(max) = CONCAT(
      ';WITH temp AS
      (
         SELECT *, 
         CONCAT(''','BookID',''' ,row_number() OVER(PARTITION BY bgm.Id ORDER BY bgm.BookId)) AS BookGroupId,
         CONCAT(''','BookGroupGenre',''' ,row_number() OVER(PARTITION BY bgm.Id ORDER BY bgm.BookId)) AS BookGroupGenre
         FROM #BookGenreMapping bgm
      )
      SELECT pt1.Id, ',@HeaderAll,' FROM
      (
          SELECT Id, ',@ColumnPivotBookId,' FROM 
          (
            SELECT t.Id, t.BookId, t.BookGroupId FROM temp t
          ) src
          PIVOT
          (
            MIN(BookId) FOR BookGroupId IN (',@ColumnPivotBookId,')    
          ) pvt
       ) pt1
       INNER JOIN
       (
          SELECT Id, ',@ColumnPivotGenre,' FROM 
          (
            SELECT t.Id, t.Genre, t.BookGroupGenre FROM temp t
          ) src
          PIVOT
          (
            MIN(Genre) FOR BookGroupGenre IN (',@ColumnPivotGenre,')    
          ) pvt
       ) pt2 ON pt1.Id = pt2.Id'
       )

PRINT @query
exec(@query)


DROP TABLE #BookGenreMapping

Answer 2

For all those with a case similar to mine.

The below link is a huge help :

https://community.spiceworks.com/topic/973727-sql-how-to-convert-multiple-rows-into-single-rows-with-more-columns