如何在SQL Server中将查询返回的指定行数据横向展示？

嘿，我来帮你搞定这个行转列的需求！针对你现在的查询场景，我们可以用SQL Server自带的PIVOT运算符来实现横向展示，分两种常用场景给你具体方案：

一、仓库列表固定？用静态PIVOT快速实现 #

如果你的仓库（wareh）是固定的几个值（比如DENVER、PA、FLORIDA这些），直接写静态PIVOT就可以搞定：

WITH OriginalData AS (
    SELECT 
        inv.refe, 
        co.color, 
        inv.size, 
        SUM(acu.total) AS total_qty,
        bo.wareh
    FROM inv_article inv 
    INNER JOIN inv_colors co ON inv.color = co.color 
    INNER JOIN inv_store acu ON inv.item = acu.item 
    INNER JOIN inv_bods bo ON bo.wareh = acu.wareh 
    WHERE refers = 'julios' AND acu.year = '2018' 
    GROUP BY inv.refe, co.color, inv.size, bo.wareh 
    HAVING SUM(total) != 0
)
SELECT 
    refe,
    color,
    size,
    ISNULL([DENVER], 0) AS DENVER_QTY, -- 用ISNULL把空值转成0，更直观
    ISNULL([PA], 0) AS PA_QTY,
    ISNULL([FLORIDA], 0) AS FLORIDA_QTY
    -- 有其他固定仓库的话，照着上面的格式继续加就行
FROM OriginalData
PIVOT (
    SUM(total_qty) -- 指定要聚合的数值列
    FOR wareh IN ([DENVER], [PA], [FLORIDA]) -- 把哪些仓库值转成列
) AS PivotedData
ORDER BY refe, color, size;

为啥这么写？ #

• 先通过CTEOriginalData把你原来的查询结果整理成一个清晰的数据集，给聚合后的数量起了个total_qty的别名，方便后续引用；
• PIVOT的作用就是把原来按wareh分行的数据，转成以仓库名为列的横向结构，聚合方式用SUM刚好匹配你原来的统计逻辑；
• 加ISNULL是为了避免某个仓库没有对应数据时显示NULL，换成0看起来更舒服。

二、仓库列表不固定？用动态SQL灵活适配 #

如果你的仓库是动态新增的，没办法提前写死所有仓库名，那就得用动态SQL来自动生成PIVOT语句：

DECLARE @WarehouseList NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- 第一步：把所有需要转置的仓库名拼接成符合语法的格式（比如[DENVER], [PA]）
SELECT @WarehouseList = STRING_AGG(QUOTENAME(wareh), ', ')
FROM (
    SELECT DISTINCT bo.wareh
    FROM inv_article inv 
    INNER JOIN inv_store acu ON inv.item = acu.item 
    INNER JOIN inv_bods bo ON bo.wareh = acu.wareh 
    WHERE refers = 'julios' AND acu.year = '2018'
) AS Warehouses;

-- 第二步：动态拼接完整的PIVOT查询语句
SET @SQL = N'
WITH OriginalData AS (
    SELECT 
        inv.refe, 
        co.color, 
        inv.size, 
        SUM(acu.total) AS total_qty,
        bo.wareh
    FROM inv_article inv 
    INNER JOIN inv_colors co ON inv.color = co.color 
    INNER JOIN inv_store acu ON inv.item = acu.item 
    INNER JOIN inv_bods bo ON bo.wareh = acu.wareh 
    WHERE refers = ''julios'' AND acu.year = ''2018'' 
    GROUP BY inv.refe, co.color, inv.size, bo.wareh 
    HAVING SUM(total) != 0
)
SELECT 
    refe,
    color,
    size,
    ' + @WarehouseList + '
FROM OriginalData
PIVOT (
    SUM(total_qty)
    FOR wareh IN (' + @WarehouseList + ')
) AS PivotedData
ORDER BY refe, color, size;';

-- 第三步：执行动态生成的SQL
EXEC sp_executesql @SQL;

注意事项： #

• STRING_AGG是SQL Server 2017及以上版本才支持的函数，如果你的版本更低，换成FOR XML PATH的方式来拼接仓库列表：

  SELECT @WarehouseList = STUFF((
      SELECT ', ' + QUOTENAME(wareh)
      FROM (SELECT DISTINCT bo.wareh FROM inv_article inv INNER JOIN inv_store acu ON inv.item = acu.item INNER JOIN inv_bods bo ON bo.wareh = acu.wareh WHERE refers = 'julios' AND acu.year = '2018') AS Warehouses
      FOR XML PATH(''), TYPE
  ).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
  

• 动态SQL的好处是不管新增多少仓库，都能自动适配，不用手动修改语句。

效果示例 #

假设你原来的查询结果是：

JULIOS BLUE 35 1,00 DENVER
JULIOS BLUE 35 1,00 PA
JULIOS BLUE 36 1,00 FLORIDA
JULIOS BLUE 36 2,00 DENVER

用静态PIVOT后会得到这样的横向结果：

这样就完美把原来按仓库分行的数据转成了横向展示的格式，符合你的需求~

内容的提问来源于stack exchange，提问作者GADI ROSALES