在 sql server 中进行数据透视 [英] Pivot in sql server

问题描述

是的，我已经尝试过代码.我的要求是用户输入年份和月份&价格在该年和月的列中按日期显示，第一列为竞争对手 ID.我想要我的结果:

<上一页>竞争对手 |第一天 |第二天 |第 3 天 |第 4 天 ....................| 第 31 天===================================================================竞争对手1|价格 |价格 |价格 |价格........|价格竞争对手2|价格 |价格 |价格 |价格........|价格竞争对手3|价格 |价格 |价格 |价格........|价格竞争对手 4|价格 |价格 |价格 |价格........|价格

我的表结构是:

COMPETITORDETAIL(ID、CompetitorID、oDate、价格)

解决方案

这样就容易多了.我编写了一个名为 pivot_query 的存储过程，它使 PIVOT 更容易用于 SQL Server 2005+.proc 的来源是 here，一些如何使用它的例子是 这里.

对于您的代码示例:

创建表竞争者(ComppetitorId 整数标识，名称 varchar(30))插入竞争对手值('Bobs Discount Emporium')去插入竞争对手的价值观('乔斯真的很便宜')去创建表竞争对手详细信息(Id 整数标识，竞争对手 ID 整数，o日期日期时间，价格小数(12,3))插入 CompetitorDetail 值 (1, getdate()-10, 10.00)去插入 CompetitorDetail 值 (1, getdate()-10, 10.00)去插入 CompetitorDetail 值 (1, getdate()-10, 10.00)去插入 CompetitorDetail 值 (1, getdate()-10, 10.00)去插入 CompetitorDetail 值 (1, getdate()-8, 11.00)去插入 CompetitorDetail 值 (1, getdate()-8, 11.00)去插入 CompetitorDetail 值 (1, getdate()-8, 11.00)去插入 CompetitorDetail 值 (1, getdate()-8, 11.00)去插入 CompetitorDetail 值 (1, getdate()-6, 12.00)去插入 CompetitorDetail 值 (1, getdate()-6, 12.00)去插入 CompetitorDetail 值 (1, getdate()-6, 12.00)去插入 CompetitorDetail 值 (1, getdate()-2, 13.00)去插入 CompetitorDetail 值 (1, getdate()-2, 13.00)去插入 CompetitorDetail 值 (1, getdate()-2, 13.00)去插入 CompetitorDetail 值 (2, getdate()-10, 14.00)去插入 CompetitorDetail 值 (2, getdate()-10, 14.00)去插入 CompetitorDetail 值 (2, getdate()-10, 14.00)去插入 CompetitorDetail 值 (2, getdate()-10, 14.00)去插入 CompetitorDetail 值 (2, getdate()-8, 15.00)去插入 CompetitorDetail 值 (2, getdate()-8, 15.00)去插入 CompetitorDetail 值 (2, getdate()-8, 15.00)去插入 CompetitorDetail 值 (2, getdate()-8, 15.00)去插入 CompetitorDetail 值 (2, getdate()-6, 16.00)去插入 CompetitorDetail 值 (2, getdate()-6, 16.00)去插入 CompetitorDetail 值 (2, getdate()-6, 16.00)去插入 CompetitorDetail 值 (2, getdate()-2, 18.00)去插入 CompetitorDetail 值 (2, getdate()-2, 18.00)去插入 CompetitorDetail 值 (2, getdate()-2, 18.00)去声明@mySQL varchar(MAX)设置@mySQL = '选择c.姓名，对(演员表(月(cd.oDate)+ 100 作为 varchar(3)),2) + ''_'' + 对(演员表(天(cd.oDate)+ 100 作为 varchar(3)),2) 周一,cd.价格从竞争对手 c加入竞争对手详情 cd上(cd.CompetitorId = c.CompetitorId)';exec pivot_query @mySQL, 'Name', 'Mon_Day', 'max(Price) MaxP,min(Price) MinP'

导致:

名称 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 折扣商场 10.000 10.000 11.000 11.000 12.000 12.000 13.000 13.000乔斯真的很便宜的废话 14.000 14.000 15.000 15.000 16.000 16.000 18.000 18.000

希望有帮助！

Yes I've tried the code. My requirement is that user inputs Year and Month & prices are shown date-wise in columns for that year and month, with first column as CompetitorID. I want my result like:

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

My Table structure is:

COMPETITORDETAIL (ID, CompetitorID, oDate, Price)

解决方案

This is a lot easier. I wrote a stored proc named pivot_query that makes PIVOT a lot easier to use for SQL Server 2005+. The source for the proc is here, some examples how to use it are here.

For your code example:

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'

which results in:

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       

Hope that helps!

这篇关于在 sql server 中进行数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！