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

this might help: http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx — Looking_for_answers, Mar 30 '17 at 06:56
@Looking_for_answers the tables/code in the page are not visible — HelpWanted, Mar 30 '17 at 07:17
Please look at McNets comment it will take you to another question like this and you may find what you are looking for. — Looking_for_answers, Mar 30 '17 at 07:24
@helpwanted161 *max number of repetitions* implies a dynamic number of repititions. In that case, you need a dynamic pivot. — Zohar Peled, Mar 30 '17 at 07:37
Not an exact duplicate.. And Pivot is not the solution here. — Shakeer Mirza, Mar 30 '17 at 08:24
Usually this is *easy* to do in a reporting tool or an application front-end, but complex to do in a T-SQL stored procedure. Is there a good **reason** why you're trying to do it down in the database? — Damien_The_Unbeliever, Mar 30 '17 at 09:38
@Damien_The_Unbeliever cause what I showed is just an example. I have about 60 columns that are generated from several databases across different servers and this is just a chunk of it. Plus going forward for any alterations (they will occur) it would be easier for me to edit within sql server. — HelpWanted, Mar 30 '17 at 09:50

score 1 · Answer 1 · answered Mar 30 '17 at 10:51

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

score 0 · Accepted Answer · answered Mar 30 '17 at 09:53

0

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

answered Mar 30 '17 at 09:53

HelpWanted

71
1
9

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

2 Answers2