IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB
GO
CREATE TABLE TB([ANAME] NVARCHAR(1),[ANUM] DECIMAL(18,1),[ADATE] DATETIME)
Insert TB
SELECT N'A',10,'2008-01-12' UNION ALL
SELECT N'A',11,'2008-01-15' UNION ALL
SELECT N'A',11,'2008-02-01' UNION ALL
SELECT N'A',11,'2008-02-20' UNION ALL
SELECT N'A',12,'2008-02-23' UNION ALL
SELECT N'A',13,'2008-03-01' UNION ALL
SELECT N'A',13,'2008-03-11' UNION ALL
SELECT N'A',13,'2008-03-21' UNION ALL
SELECT N'A',9,'2008-05-15' UNION ALL
SELECT N'A',7.5,'2008-07-15'
Go
SELECT * FROM TBDECLARE @S nvarchar(4000)
SET @S='SELECT ANAME'
SELECT @S=@S+','+QUOTENAME(ANUM)
+N'=CONVERT(VARCHAR(10),MIN(CASE ANUM WHEN '+QUOTENAME(ANUM,N'''')
+N' THEN ADATE END),120)'
FROM TB
GROUP BY ANUMEXEC (@S+N'FROM TB GROUP BY ANAME')/*ANAME 7.5 9.0 10.0 11.0 12.0 13.0
----- ---------- ---------- ---------- ---------- ---------- ----------
A 2008-07-15 2008-05-15 2008-01-12 2008-01-15 2008-02-23 2008-03-01
*/
GO
CREATE TABLE TB([ANAME] NVARCHAR(1),[ANUM] DECIMAL(18,1),[ADATE] DATETIME)
Insert TB
SELECT N'A',10,'2008-01-12' UNION ALL
SELECT N'A',11,'2008-01-15' UNION ALL
SELECT N'A',11,'2008-02-01' UNION ALL
SELECT N'A',11,'2008-02-20' UNION ALL
SELECT N'A',12,'2008-02-23' UNION ALL
SELECT N'A',13,'2008-03-01' UNION ALL
SELECT N'A',13,'2008-03-11' UNION ALL
SELECT N'A',13,'2008-03-21' UNION ALL
SELECT N'A',9,'2008-05-15' UNION ALL
SELECT N'A',7.5,'2008-07-15'
Go
SELECT * FROM TBDECLARE @S nvarchar(4000)
SET @S='SELECT ANAME'
SELECT @S=@S+','+QUOTENAME(ANUM)
+N'=CONVERT(VARCHAR(10),MIN(CASE ANUM WHEN '+QUOTENAME(ANUM,N'''')
+N' THEN ADATE END),120)'
FROM TB
GROUP BY ANUMEXEC (@S+N'FROM TB GROUP BY ANAME')/*ANAME 7.5 9.0 10.0 11.0 12.0 13.0
----- ---------- ---------- ---------- ---------- ---------- ----------
A 2008-07-15 2008-05-15 2008-01-12 2008-01-15 2008-02-23 2008-03-01
*/
数据:
A 10 2008-01-12
A 11 2008-01-15
A 11 2008-02-01
A 11 2008-02-20
A 12 2008-02-23
A 13 2008-03-01
A 13 2008-03-11
A 13 2008-03-21
A 9 2008-05-15
A 7.5 2008-07-15
A 12 2008-08-23
A 11 2008-09-01
A 13 2008-10-11
B 12 2008-07-23
B 11 2008-08-01
B 13 2008-10-11
报表(括弧日期可以不显示,主要是对应单价的时间顺序)
编码 单价1 单价2 单价3 单价4 单价5 单价6 单价7 单价8 单价9
A 10 11 12 13 9 7.5 12 11 13
( 2008-01-12 2008-01-15 2008-02-23 2008-03-01 2008-05-15 2008-07-15)
B 12 11 13
(2008-07-23 2008-08-01 2008-10-11)
insert into tb values('A' , '10' , '2008-01-12')
insert into tb values('A' , '11' , '2008-01-15')
insert into tb values('A' , '11' , '2008-02-01')
insert into tb values('A' , '11' , '2008-02-20')
insert into tb values('A' , '12' , '2008-02-23')
insert into tb values('A' , '13' , '2008-03-01')
insert into tb values('A' , '13' , '2008-03-11')
insert into tb values('A' , '13' , '2008-03-21')
insert into tb values('A' , '9' , '2008-05-15')
insert into tb values('A' , '7.5' , '2008-07-15')
godeclare @sql varchar(8000)
set @sql = 'select col1 '
select @sql = @sql + ' , max(case col2 when ''' + cast(col2 as varchar) + ''' then col3 else '''' end) [' + cast(col2 as varchar) + ']'
from (select distinct col2 from tb) as a
set @sql = @sql + ' from tb group by col1'
exec(@sql) drop table tb/*
col1 10 11 12 13 7.5 9
---------- ---------- ---------- ---------- ---------- ---------- ----------
A 2008-01-12 2008-02-20 2008-02-23 2008-03-21 2008-07-15 2008-05-15
*/
我要的是如下的效果
能否在提示一下呢
A 10 11 12 13 9 7.5 12 11 13
B 12 11 13
这不是字段名,而是记录值
A 10 11 12 13 9 7.5 12 11 13
B 12 11 13
C 8 15 11 12 5 12 13 20
D 12 18 23 22 18 21 12
编码 单价1 单价2 单价3 单价4 单价5 单价6 单价7 单价8
怎么能是int 或者 varchar
下面是 datetime呢A 10 11 12 13 9 7.5 12 2008-01-12 2008-01-15 2008-02-23 2008-03-01 2008-05-15 2008-07-15我是菜鸟 随便说说