sql server 中的数据透视表

Question

是的，我已经尝试过代码。我的要求是用户输入年份和月份&价格按日期显示在当年和月份的列中，第一列为 CompetitorID。我希望我的结果如下：

Competitors | day1  | day2  | day3  | day4 ..............|day31
================================================================
competitor 1| Price | Price | price | price..............|price 
competitor 2| Price | Price | price | price..............|price
competitor 3| Price | Price | price | price..............|price 
competitor 4| Price | Price | price | price..............|price

我的表结构是：

COMPETITORDETAIL (ID, CompetitorID, oDate, Price)

Answer 1

这容易多了。我编写了一个名为 pivot_query 的存储过程，它使 PIVOT 在 SQL Server 2005+ 中更易于使用。该过程的来源是这里，一些如何使用它的示例是< a href="http://dot-dash-dot.com/files/pivot_query_examples.sql" rel="nofollow noreferrer">此处。

对于您的代码示例：

create table Competitors
   (
   CompetitorId      integer identity,
   Name              varchar(30)
   )

insert into Competitors values ('Bobs Discount Emporium')
go
insert into Competitors values ('Joes Really Cheap Crap')
go

create table CompetitorDetail
   (
   Id                integer identity,
   CompetitorId      integer,
   oDate             datetime,
   Price             decimal(12,3)
   )

insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-6, 12.00)
go
insert into CompetitorDetail values (1, getdate()-6, 12.00)
go
insert into CompetitorDetail values (1, getdate()-6, 12.00)
go
insert into CompetitorDetail values (1, getdate()-2, 13.00)
go
insert into CompetitorDetail values (1, getdate()-2, 13.00)
go
insert into CompetitorDetail values (1, getdate()-2, 13.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-6, 16.00)
go
insert into CompetitorDetail values (2, getdate()-6, 16.00)
go
insert into CompetitorDetail values (2, getdate()-6, 16.00)
go
insert into CompetitorDetail values (2, getdate()-2, 18.00)
go
insert into CompetitorDetail values (2, getdate()-2, 18.00)
go
insert into CompetitorDetail values (2, getdate()-2, 18.00)
go

declare @mySQL varchar(MAX)

set @mySQL = '
select
   c.Name,
   right(cast(month(cd.oDate) + 100 as varchar(3)),2) + ''_'' + right(cast(day(cd.oDate) + 100  as varchar(3)),2) mon_day,
   cd.Price
from
   Competitors c

   JOIN CompetitorDetail cd
      on (cd.CompetitorId = c.CompetitorId )
   ';

exec pivot_query @mySQL, 'Name', 'Mon_Day', 'max(Price) MaxP,min(Price) MinP'

结果是：

Name                           01_09_MaxP   01_09_MinP   01_11_MaxP   01_11_MinP   01_13_MaxP   01_13_MinP   01_17_MaxP   01_17_MinP   
------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ 
Bobs Discount Emporium         10.000       10.000       11.000       11.000       12.000       12.000       13.000       13.000       
Joes Really Cheap Crap         14.000       14.000       15.000       15.000       16.000       16.000       18.000       18.000       

希望有帮助！

Answer 2

如果您使用 Microsoft SQL-Server，则有一个枢轴函数。但列是静态定义的，因此它的使用受到限制。 （请参阅http://technet.microsoft.com/en-us/library/ ms177410.aspx）

有动态SQL的解决方案，但我最终在代码中解决了这个问题。

Answer 3

对于 SQL Server 2005、2008

要测试的表和一些数据

CREATE TABLE CompetitorDetail
  ( 
   ID int
  ,CompetitorID int
  ,oDate datetime
  ,Price decimal(19, 4)
  )

INSERT INTO  CompetitorDetail
      ( ID, CompetitorID, oDate, Price )
SELECT  1, 1, '2010-01-01', 100.0 UNION
SELECT  2, 1, '2010-01-02', 110.0 UNION
SELECT  3, 1, '2010-01-03', 99.0 UNION
SELECT  4, 2, '2010-01-01', 102.2 UNION
SELECT  5, 2, '2010-01-02', 112.2 UNION
SELECT  6, 2, '2010-01-03', 99.2 UNION
SELECT  7, 3, '2010-01-01', 100.3 UNION
SELECT  8, 3, '2010-01-02', 110.3 UNION
SELECT  9, 3, '2010-01-03', 99.3 ;

周期的开始和天数

/* First day of the peroid */
DECLARE @StartDate datetime
    ,@NumberOfDays int

SET @StartDate = '2010-01-01'
SET @NumberOfDays = 31

动态列 = 动态 sql

/* Table to compose dynamic query */
DECLARE @qw TABLE
  ( 
   id int IDENTITY(1, 1)
  ,txt nvarchar(500)
  )

/* Start composing dynamic query */
INSERT  INTO @qw ( txt ) VALUES  ( 'SELECT' ) 
INSERT  INTO @qw ( txt ) VALUES  ( 'CompetitorID' )

继续编写动态查询

/* Helpers */
DECLARE
  @dte datetime
 ,@str varchar(10)
 ,@i int


/* Compose dynamic query */
SET @i = 0
WHILE @i < @NumberOfDays 
  BEGIN
    SET @dte = DATEADD(d, @i, @StartDate)
    SET @str = CONVERT(varchar(10), @dte, 121)  
    INSERT  INTO @qw ( txt )
            SELECT  ',MAX(CASE oDate WHEN ''' + @str + ''' THEN Price ELSE NULL END) AS [' + @str + ']'

    SET @i = @i + 1
  END

/* Finish the dynamic query */
INSERT  INTO @qw (txt) VALUES ( 'FROM  CompetitorDetail' )
INSERT  INTO @qw (txt) VALUES ( 'GROUP BY CompetitorID' )
INSERT  INTO @qw (txt) VALUES ( 'ORDER BY CompetitorID' )

连接到变量并执行

/* Create a variable with dynamic sql*/
DECLARE @exe nvarchar(4000)
SET @exe=''
SELECT  @exe = @exe + txt + ' ' FROM @qw ORDER BY id


/* execute dynamic sql */
EXEC sp_executesql @exe

返回

CompetitorID 2010-01-01   2010-01-02   2010-01-03   2010-01-04  ...  2010-01-31 
------------ ----------   ----------   ----------   ----------  ...  ---------- 
1            100.0000     110.0000     99.0000      NULL        ...  NULL       
2            102.2000     112.2000     99.2000      NULL        ...  NULL       
3            100.3000     110.3000     99.3000      NULL        ...  NULL       

Answer 4

CASE 
WHEN SQL Server 2005 OR 2008 THEN Use Pivot 
WHEN Oracle THEN Use LIKE MAX(Decode(Day=1,Data,0) as Day1 and GROUP BY Day
WHEN SQL Server 2000 THEN Use LIKE MAX(CASE WHEN Day = 1 THEN Data ELSE 0 End) As Day1 and GROUP BY Day 
END