sql-server - 如何在表格面板中动态显示数据

Question

我正在使用 C# 开发一个 Windows 应用程序。在这种情况下，我想在表格布局面板中显示类别明智的书籍图像。对于每个类别，我想显示 5 张书籍图像。

我用来检索类别明智的书的查询是

WITH CategoryCTE AS (
    SELECT 
        BookName,
        ROW_NUMBER() OVER (
            PARTITION BY CategoryName
            ORDER BY CategoryName DESC
        ) AS CTE_Order
    FROM BookMaster
)
SELECT bm.BookName,bm.BookImage, cm.CategoryName, scm.SubCategoryName
FROM 
    CategoryCTE 
    INNER JOIN BookMaster bm ON CategoryCTE.BookName = bm.BookName
    INNER JOIN CategoryMaster cm ON bm.CategoryName = cm.Oid
    INNER JOIN SubCategoryMaster scm ON bm.SubCategoryName = scm.Oid
WHERE CategoryCTE.CTE_Order < 6
GROUP BY cm.CategoryName, scm.SubCategoryName, bm.BookName,bm.BookImage

此查询为我提供了正确的输出，即它为我提供了每个类别的 5 本书。但问题是，我想在表格面板中显示书籍图像，即第 1 行将有 5 个类别 1 的书籍图像，第 2 行有类别 2 的书籍图像等等。现在我的数据库中有 4 个书籍类别。那表示表格布局面板的行取决于数据库中的类别表，表格的列是固定的，即第一列是类别名称，其他 5 列是书籍图片。

我希望我已经正确解释了我的问题。请为此提出任何解决方案。提前致谢。

Answer 1

如果您希望结果看起来像这样:

CategoryName   Book1         Book2             Book3           Book4          Book5
------------   -----------   ---------------   --------------  -------------  ------------
Cat1           Image#6.jpg   Image#61.jpg      Image#62.jpg    Image#63.jpg   Image#64.jpg
Cat2           Image#5.jpg   Image#51316.jpg   Image#5136.jpg  Image#516.jpg  Image#56.jpg

应该执行以下查询:

WITH CategoryCTE AS (
    SELECT 
        BookName,
        ROW_NUMBER() OVER (
            PARTITION BY CategoryName
            ORDER BY CategoryName DESC, BookName
        ) AS CTE_Order
    FROM BookMaster    
)

SELECT CategoryName, [1] AS Book1, [2] AS Book2, [3] AS Book3, [4] AS Book4, [5] AS Book5
FROM 
(SELECT bm.BookImage, cm.CategoryName, c.CTE_Order FROM 
    CategoryCTE c
    INNER JOIN BookMaster bm ON c.BookName = bm.BookName
    INNER JOIN CategoryMaster cm ON bm.CategoryName = cm.Oid
    INNER JOIN SubCategoryMaster scm ON bm.SubCategoryName = scm.Oid
WHERE c.CTE_Order < 6
GROUP BY cm.CategoryName, bm.BookImage, c.CTE_Order
) Sub
PIVOT (MAX(BookImage) FOR CTE_Order IN ([1],[2],[3], [4],[5])) AS PivotTable