我以前通过使用子查询实现了同样的目的。所以如果你原来的表叫做sto叙述byweek，你有一个单独的表列出商店id，然后它看起来像这样:

SELECT StoreID, 
    Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),
    Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),
    Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)
FROM Store
ORDER BY StoreID

这种方法的一个优点是语法更清晰，并且更容易连接到其他表，将其他字段也拉到结果中。

我的轶事结果是，在不到一秒钟的时间内运行这个查询的几千行，实际上我有7个子查询。但是正如评论中所指出的，这样做的计算成本更高，所以如果您希望它在大量数据上运行，请谨慎使用此方法。

如果您使用的是SQL Server 2005+，那么您可以使用PIVOT函数将数据从行转换为列。

听起来，如果星期是未知的，您将需要使用动态sql，但最初使用硬编码版本更容易看到正确的代码。

首先，这里有一些快速的表定义和数据:

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);
    
INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

如果你的值是已知的，那么你将硬编码查询:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

参见SQL Demo

然后如果你需要动态生成周数，你的代码将是:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

参见SQL Demo。

动态版本生成应转换为列的周数列表。两者都给出了相同的结果:

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |

你可以这样做:

SELECT * 
FROM yourTable
PIVOT (MAX(xCount) 
       FOR Week in ([1],[2],[3],[4],[5],[6],[7])) AS pvt

DEMO

Pivot是SQL操作符之一，用于将输出中的唯一数据从一列转换为多列。这也意味着将行转换为列(旋转表)。让我们考虑这张桌子，

如果我想根据每个客户的产品类型(扬声器、眼镜、耳机)过滤这些数据，那么使用Pivot操作符。

Select CustmerName, Speaker, Glass, Headset  
from TblCustomer  
   Pivot  
    (  
     Sum(Price) for Product in ([Speaker],[Glass],[Headset])  
    ) as PivotTable 

这是动态的# of weeks。

完整示例:SQL动态Pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Week)
FROM (SELECT DISTINCT Week FROM #StoreSales) AS Weeks

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT Store, ' + @ColumnName + ' 
    FROM #StoreSales
    PIVOT(SUM(xCount) 
          FOR Week IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

我以前通过使用子查询实现了同样的目的。所以如果你原来的表叫做sto叙述byweek，你有一个单独的表列出商店id，然后它看起来像这样:

SELECT StoreID, 
    Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),
    Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),
    Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)
FROM Store
ORDER BY StoreID

这种方法的一个优点是语法更清晰，并且更容易连接到其他表，将其他字段也拉到结果中。

我的轶事结果是，在不到一秒钟的时间内运行这个查询的几千行，实际上我有7个子查询。但是正如评论中所指出的，这样做的计算成本更高，所以如果您希望它在大量数据上运行，请谨慎使用此方法。