只是给你一些其他数据库如何解决这个问题的想法。DolphinDB还内置了对旋转的支持，sql看起来更加直观和整洁。它非常简单，只需指定键列(Store)、旋转列(Week)和计算的度量(sum(xCount))。

//prepare a 10-million-row table
n=10000000
t=table(rand(100, n) + 1 as Store, rand(54, n) + 1 as Week, rand(100, n) + 1 as xCount)

//use pivot clause to generate a pivoted table pivot_t
pivot_t = select sum(xCount) from t pivot by Store, Week

DolphinDB是一个柱状的高性能数据库。演示中的计算成本在戴尔xps笔记本电脑(i7 cpu)上低至546毫秒。欲了解更多详细信息，请参阅DolphinDB在线手册https://www.dolphindb.com/help/index.html?pivotby.html

以下是上面@Tayrn回答的修订版，可能会帮助你更容易理解旋转:

这可能不是最好的方法，但这帮助我理解了如何透视表。

ID =要主元的行数

MY_KEY =您要从原始表中选择的列，其中包含您想要进行主元转换的列名。

VAL =您希望在每列下返回的值。

MAX(VAL) =>可替换为其他聚合函数。Sum (val)， min (val)，等等…

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(MY_KEY) 
                from yt
                group by MY_KEY
                order by MY_KEY ASC
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
set @query = 'SELECT ID,' + @cols + ' from 
         (
            select ID, MY_KEY, VAL 
            from yt
        ) x
        pivot 
        (
            sum(VAL)
            for MY_KEY in (' + @cols + ')
        ) p '

        execute(@query);

只是给你一些其他数据库如何解决这个问题的想法。DolphinDB还内置了对旋转的支持，sql看起来更加直观和整洁。它非常简单，只需指定键列(Store)、旋转列(Week)和计算的度量(sum(xCount))。

//prepare a 10-million-row table
n=10000000
t=table(rand(100, n) + 1 as Store, rand(54, n) + 1 as Week, rand(100, n) + 1 as xCount)

//use pivot clause to generate a pivoted table pivot_t
pivot_t = select sum(xCount) from t pivot by Store, Week

DolphinDB是一个柱状的高性能数据库。演示中的计算成本在戴尔xps笔记本电脑(i7 cpu)上低至546毫秒。欲了解更多详细信息，请参阅DolphinDB在线手册https://www.dolphindb.com/help/index.html?pivotby.html

这是动态的# 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

你可以这样做:

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

DEMO

如果您使用的是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 |