Query to produce rows that can "loop" over a collection up to N?-CodePudding

Let's say you have two tables, Students and Books.

Students:

StudentId	Name
1	Amy
2	John

Books:

BookId	Title	StudentId
1	A	1
2	B	1
3	C	1
4	D	1
5	E	1
6	F	1
7	G	1
8	H	2
9	I	2
10	J	2

I want to be able to return a query like this. This query needs to be formatted in this way to work with another program. In this case, "N" would be 4, to control how Books populate per row. I would appreciate any help/advice on this.

Query:

Name	Book1	Book2	Book3	Book4
Amy	A	B	C	D
Amy	E	F	G
John	H	I	J

CodePudding user response：

Here is a clean solution based on a single row_number pivot

with 
book_positions as
(
    select Title
          ,StudentId
          ,i/4   1 as row
          ,i%4   1 as col
          
    from  (select Title
                 ,StudentId
                 ,row_number() over (partition by StudentId order by Title) - 1 as i
                 
           from   books
           ) b
)
select     s.Name
          ,p.[1] as Book1
          ,p.[2] as Book2
          ,p.[3] as Book3
          ,p.[4] as Book4
      
from                 Students        as s 
           left join book_positions as b 
                         pivot (max(b.Title) for b.col in ([1],[2],[3],[4])) as p
           on        s.StudentId = p.StudentId
       
order by   s.Name
          ,p.row

Name	Book1	Book2	Book3	Book4
Amy	A	B	C	D
Amy	E	F	G
John	H	I	J

Fiddle

CodePudding user response：

If the column number is predetermined (which it seems like it is), just some simple ROW_NUMBER() should do the trick. No need for dynamic SQL

Pivot to 4 Columns without Looping

DROP TABLE IF EXISTS #tbl_student
DROP TABLE IF EXISTS #tbl_book

CREATE TABLE #tbl_student(StudentId INT IDENTITY(1,1),Name Varchar(100))
INSERT INTO #tbl_student
VALUES ('Amy'),('John')

CREATE TABLE #tbl_book(BookId INT IDENTITY(1,1),Title Varchar(100),StudentId INT)
INSERT INTO #tbl_book VALUES
('A',1)
,('B',1)
,('C',1)
,('D',1)
,('E',1)
,('F',1)
,('G',1)
,('H',2)
,('I',2)
,('J',2)

;WITH cte_BookGroup AS (
    SELECT 
        A.StudentID
        ,A.Name
        /*Numbers the books per student*/
        ,RowNum = ROW_NUMBER() OVER (PARTITION BY B.StudentID ORDER BY B.Title)
        /*Will be used to creates 1 row for every 4 books per student*/
        ,RowGroupID = (ROW_NUMBER() OVER (PARTITION BY B.StudentID ORDER BY B.Title)   3) / 4 
        ,Title
    FROM #tbl_student AS A
    INNER JOIN #tbl_book AS B
        ON A.StudentID = B.StudentID
)


SELECT 
    A.StudentID
    ,A.[Name]
    ,Book1 = MAX(CASE WHEN A.RowNum % 4 = 1 THEN A.Title END)
    ,Book2 = MAX(CASE WHEN A.RowNum % 4 = 2 THEN A.Title END)
    ,Book3 = MAX(CASE WHEN A.RowNum % 4 = 3 THEN A.Title END)
    ,Book4 = MAX(CASE WHEN A.RowNum % 4 = 0 THEN A.Title END)
FROM cte_BookGroup AS A
GROUP BY A.StudentID,A.[Name],A.RowGroupID
ORDER BY A.StudentID,A.RowGroupID