是的。 我的要求是,用户投入年数和月数和月数;价格在当年和月的栏目中显示,第一栏是竞争主管机构。 我希望我的结果是:
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
我的表格结构是:
COMPITORDETAIL(ID, CompetitorID, oDate, Price>
这非常容易。 我写了一幅名为pivot_query<>/strong>的储存纸,使PIVOT更容易为2005年+服务器使用。 The source for the proc is here, some example how to use it are 。
您的法典例:
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
希望帮助!
如果你与Microsoft-Server合作,就可发挥ot的作用。 但各栏是固定的,因此其使用有限。 (见)
有一些充满活力的解决办法,但最终在法典中解决这一问题。
2005年,日内瓦
表和一些用于测试的数据
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
动态栏 = 动态元
/* 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 )
1. 装成一个变量并加以执行
/* 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
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
I m trying to export some tables from SQL Server 2005 and then create those tables and populate them in Oracle. I have about 10 tables, varying from 4 columns up to 25. I m not using any constraints/...
In our database we have an SQL server account that has the correct roles to access some of the databases. We are now switching to windows authentication and I was wondering if we can create a NT user ...
I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far. The stored procedure has 1 input and 1 output parameter. With every combination I ...
Is it possible to use the sqlcmd command to dump table structure the way it can be done with mysqldump?
Hi I have the following SP, however when I use LINQ to SQL it generates 2 multiple recordsets. For my sanity I am trying to fathom out what it is in the stored procedure that is doing this and would ...
I want to create a table in MS SQL Server 2005 to record details of certain system operations. As you can see from the table design below, every column apart from Details is is non nullable. CREATE ...
I love the new DATE datatype in SQL Server 2008, but when I compare a DATE field to a DATETIME field on a linked server (SQL 2005, in this case), like this: DECLARE @MyDate DATE SET @MyDate = CONVERT(...
How to determine if a Transaction is active i.e. before issuing Begin Transaction I want to ensure that no previous transaction are open.. the platform is VB6, MS-SQL Server 2000 and ADO 2.8