在SQL Server中使用'Pivot'将行转换为列

7 回答

• 291

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

  动态版本，生成应转换为列的 week 数字列表 . 两者都给出了相同的结果：

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

  回复于 2024-04-27T16:07:07+08:00
• 12

  select * from (select name, ID from Empoyee) Visits
      pivot(sum(ID) for name
      in ([Emp1],
      [Emp2],
      [Emp3]
      ) ) as pivottable;
  

  回复于 2024-04-27T16:07:07+08:00
• 21

  以上是对@Tayrn答案的修订，可能有助于您更轻松地理解旋转：

  这可能不是最好的方法，但这有助于我围绕如何转动表格 .

  ID =要转动的行

  MY_KEY =您从原始表中选择的列，其中包含要转动的列名称 .

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

  MAX（VAL）=>可以替换为其他聚合函数 . SUM（VAL），MIN（VAL），ETC ......

  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);
  

  回复于 2024-04-27T16:07:07+08:00
• 6

  这是你可以做的：

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

  DEMO

  回复于 2024-04-27T16:07:07+08:00
• 1

  我正在编写一个可能对此有用的sp，基本上这个sp会旋转任何表并返回一个新的表，或仅返回数据集，这是执行它的方法：

  Exec dbo.rs_pivot_table @schema=dbo,@table=table_name,@column=column_to_pivot,@agg='sum([column_to_agg]),avg([another_column_to_agg]),',
          @sel_cols='column_to_select1,column_to_select2,column_to_select1',@new_table=returned_table_pivoted;
  

  please note 在参数@agg中，列名必须是 '[' ，参数必须以逗号 ',' 结尾

  SP

  Create Procedure [dbo].[rs_pivot_table]
      @schema sysname=dbo,
      @table sysname,
      @column sysname,
      @agg nvarchar(max),
      @sel_cols varchar(max),
      @new_table sysname,
      @add_to_col_name sysname=null
  As
  --Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';
  Begin
  
      Declare @query varchar(max)='';
      Declare @aggDet varchar(100);
      Declare @opp_agg varchar(5);
      Declare @col_agg varchar(100);
      Declare @pivot_col sysname;
      Declare @query_col_pvt varchar(max)='';
      Declare @full_query_pivot varchar(max)='';
      Declare @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica
  
      Create Table #pvt_column(
          pivot_col varchar(100)
      );
  
      Declare @column_agg table(
          opp_agg varchar(5),
          col_agg varchar(100)
      );
  
      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))
          Set @ind_tmpTbl=0;
      ELSE IF OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL
          Set @ind_tmpTbl=1;
  
      IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N'U')) OR 
          OBJECT_ID('tempdb..'+ltrim(rtrim(@new_table))) IS NOT NULL
      Begin
          Set @query='DROP TABLE '+@new_table+'';
          Exec (@query);
      End;
  
      Select @query='Select distinct '+@column+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+@schema+'.'+@table+' where '+@column+' is not null;';
      Print @query;
  
      Insert into #pvt_column(pivot_col)
      Exec (@query)
  
      While charindex(',',@agg,1)>0
      Begin
          Select @aggDet=Substring(@agg,1,charindex(',',@agg,1)-1);
  
          Insert Into @column_agg(opp_agg,col_agg)
          Values(substring(@aggDet,1,charindex('(',@aggDet,1)-1),ltrim(rtrim(replace(substring(@aggDet,charindex('[',@aggDet,1),charindex(']',@aggDet,1)-4),')',''))));
  
          Set @agg=Substring(@agg,charindex(',',@agg,1)+1,len(@agg))
  
      End
  
      Declare cur_agg cursor read_only forward_only local static for
      Select 
          opp_agg,col_agg
      from @column_agg;
  
      Open cur_agg;
  
      Fetch Next From cur_agg
      Into @opp_agg,@col_agg;
  
      While @@fetch_status=0
      Begin
  
          Declare cur_col cursor read_only forward_only local static for
          Select 
              pivot_col 
          From #pvt_column;
  
          Open cur_col;
  
          Fetch Next From cur_col
          Into @pivot_col;
  
          While @@fetch_status=0
          Begin
  
              Select @query_col_pvt='isnull('+@opp_agg+'(case when '+@column+'='+quotename(@pivot_col,char(39))+' then '+@col_agg+
              ' else null end),0) as ['+lower(Replace(Replace(@opp_agg+'_'+convert(varchar(100),@pivot_col)+'_'+replace(replace(@col_agg,'[',''),']',''),' ',''),'&',''))+
                  (case when @add_to_col_name is null then space(0) else '_'+isnull(ltrim(rtrim(@add_to_col_name)),'') end)+']'
              print @query_col_pvt
              Select @full_query_pivot=@full_query_pivot+@query_col_pvt+', '
  
              --print @full_query_pivot
  
              Fetch Next From cur_col
              Into @pivot_col;        
  
          End     
  
          Close cur_col;
          Deallocate cur_col;
  
          Fetch Next From cur_agg
          Into @opp_agg,@col_agg; 
      End
  
      Close cur_agg;
      Deallocate cur_agg;
  
      Select @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);
  
      Select @query='Select '+@sel_cols+','+@full_query_pivot+' into '+@new_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+
      @schema+'.'+@table+' Group by '+@sel_cols+';';
  
      print @query;
      Exec (@query);
  
  End;
  GO
  

  这是一个执行的例子：

  Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg='sum([val_liq]),avg([can_liq]),',@sel_cols='cod_emp,cod_con,tip_liq',@new_table=##TEMPORAL1PVT;
  

  然后 Select * From ##TEMPORAL1PVT 将返回：

  

  回复于 2024-04-27T16:07:07+08:00
• 3

  这是动态的周数 .

  这里有完整的例子：SQL Dynamic 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
  

  回复于 2024-04-27T16:07:07+08:00
• 0

  我之前通过使用子查询实现了同样的目标 . 因此，如果您的原始表名为StoreCountsByWeek，并且您有一个单独的表列出了商店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个子查询 . 但正如评论中所指出的那样，以这种方式执行它的计算成本更高，因此如果您希望它运行在大量数据上，请小心使用此方法 .

  回复于 2024-04-27T16:07:07+08:00