在 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!
相关文章