在SQL Server中枢轴 [英] Pivot in sql server
本文介绍了在SQL Server中枢轴的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是的,我已经尝试了代码.我的要求是用户输入Year and Month&.价格在该年和月份的列中按日期显示,第一列为CompetitorID.我希望我的结果像这样:
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
我的表格结构是:
COMPETITORDETAIL (ID, CompetitorID, oDate, Price)
推荐答案
这要容易得多.我编写了一个名为 pivot_query 的存储过程,该过程使PIVOT在SQL Server 2005+中更容易使用. proc的来源是此处,如何使用它的一些示例是此处.
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.
对于您的代码示例:
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
希望有帮助!
这篇关于在SQL Server中枢轴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文