table:
con1 con2
A 11
B 22
C 33
A 111
B 222
C 333
. .
. .
. .
纪录都是ABC的倍数,通过查询语句可以使得只有3条纪录,con2的值都横向显示,如下:con1 con2 con3 ...
A 11 111 ...
B 22 222 ...
C 33 333 ...
想半天没想出什么好方法。请各位帮忙下阿
con1 con2
A 11
B 22
C 33
A 111
B 222
C 333
. .
. .
. .
纪录都是ABC的倍数,通过查询语句可以使得只有3条纪录,con2的值都横向显示,如下:con1 con2 con3 ...
A 11 111 ...
B 22 222 ...
C 33 333 ...
想半天没想出什么好方法。请各位帮忙下阿
1501 A 2007-11-10 50
1501 A 2007-11-12 20
1502 B 2007-11-11 30
1502 B 2007-11-12 20
1502 B 2007-11-13 40
1503 C 2007-11-10 40
1524 C 2007-11-12 30 怎么用SQL语句得到下面的结果:
产品编号 产品名称 销售时间 数量 销售时间 数量 销售时间 数量
1501 A 2007-11-10 50 2007-11-12 20
1501 B 2007-11-11 30 2007-11-12 20 2007-11-13 40
1502 C 2007-11-10 40 2007-11-12 30
create table tb(产品编号 varchar(10) ,产品名称 varchar(10),销售时间 datetime,数量 int)
insert tb select '1501' , 'A' , '2007-11-10' , 50
insert tb select '1501' , 'A' , '2007-11-12' , 20
insert tb select '1502' , 'B' , '2007-11-11' , 30
insert tb select '1502' , 'B' , '2007-11-12' , 20
insert tb select '1502' , 'B' , '2007-11-13' , 40
insert tb select '1503' , 'C' , '2007-11-10' , 40
insert tb select '1503' , 'C' , '2007-11-12' , 30DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT 产品编号,产品名称'
SELECT @SQL=@SQL+',MAX(CASE WHEN PX='+rtrim(px)+' THEN CONVERT(CHAR(10),销售时间,120) ELSE '''' END ) [销售时间],
SUM(CASE WHEN PX='+rtrim(px)+' THEN 数量 ELSE 0 END ) [数量]'
FROM(SELECT DISTINCT PX=(SELECT COUNT(*) FROM tb where a.产品编号=产品编号 and 产品名称=a.产品名称 and 销售时间<a.销售时间)+1 FROM tb a) TSET @SQL=@SQL+' FROM (SELECT *,PX=(SELECT COUNT(*) FROM tb where a.产品编号=产品编号 and 产品名称=a.产品名称 and 销售时间<a.销售时间)+1 FROM tb a) T GROUP BY 产品编号,产品名称'EXEC (@SQL)DROP TABLE tb/*
产品编号 产品名称 销售时间 数量 销售时间 数量 销售时间 数量
---------- ---------- ---------- ----------- ---------- ----------- ---------- -----------
1501 A 2007-11-10 50 2007-11-12 20 0
1502 B 2007-11-11 30 2007-11-12 20 2007-11-13 40
1503 C 2007-11-10 40 2007-11-12 30 0(3 行受影响)
*/
create table os(con1 varchar(10),con2 int)
insert into os select 'A',11
insert into os select 'B',22
insert into os select 'C',33
insert into os select 'A',111
insert into os select 'B',222
insert into os select 'C',333
select id=identity(int,1,1),* into # from osDECLARE @SQL VARCHAR(8000),@i int
SET @SQL='SELECT con1'
set @i=1
SELECT @SQL=@SQL+',MAX(CASE WHEN PX='+rtrim(px)+' THEN con2 ELSE 0 END ) con'+ltrim(@i),@i=@i+1
FROM(select distinct px=(select count(1) from # where con1=a.con1 and id<=a.id) from # a)b
SET @SQL=@SQL+' FROM (select px=(select count(1) from # where con1=a.con1 and id<=a.id),* from # a)b GROUP BY con1'
EXEC (@SQL)
set @i=1改为set @i=2
insert into tb select 1,'起动','合格'
insert into tb select 1,'潜动','不合格'
insert into tb select 2,'起动','不合格'
insert into tb select 2,'潜动','不合格'select 表号,
max(case when 项目='起动' then 结论 end) as '起动',
max(case when 项目='潜动' then 结论 end) as '潜动'
from tb
group by 表号declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when 项目='''+项目+''' then 结论 end) as ['+项目+']'
from (select distinct 项目 from tb)tp
exec('select 表号,'+@sql+' from tb group by 表号')
A 11 111 ...
B 22 222 ...
declare @t table (con1 varchar(10),con2 int)
insert into @t select 'a', 11
insert into @t select 'b', 22
insert into @t select 'c', 33
insert into @t select 'a', 111
insert into @t select 'b', 222
insert into @t select 'c', 333 select *,id=identity(int,1,1) into #3tt from @t order by Con1select t.*,tt.con2 from #3tt t inner join (select * from #3tt where id%2=0) tt on t.con1=tt.con1 where t.id%2=1drop table #3tt
/*
a 11 1 111
b 22 3 222
c 333 5 33
*/
而不能像普通select语句来处理的吧?