求一个横向显示的SQL语句（内祥）!!!!

dgg20 2008-04-07 01:02:30

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 ...
想半天没想出什么好方法。。。。请各位帮忙下阿

...全文

84 点赞收藏 8

写回复

8 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

-狙击手- 2008-04-07

就现在这个表结构在2000下一个select是不行的

回复赞

dgg20 2008-04-07

问个比较白的问题，像1楼2楼是不是存储过程阿？我C#代码要执行的话因该用调用存储过程的形式来写吧。
而不能像普通select语句来处理的吧？

回复赞

wangxuelid 2008-04-07













con1 con2 con3 ... 

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 Con1



select 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=1



drop  table #3tt

/*

a	11	1	111

b	22	3	222

c	333	5	33

*/

回复赞

zjjxxhn19840204 2008-04-07

在站内搜索行列转化就能找到答案了，

回复赞

tianhuo_soft 2008-04-07

create table tb(表号 int, 项目 varchar(10), 结论 varchar(10))
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 表号')

回复赞

pt1314917 2008-04-07

哦，对了。应该将
set @i=1改为set @i=2

回复赞

pt1314917 2008-04-07



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 os



DECLARE @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)

回复赞

liangCK 2008-04-07

产品编号     产品名称   销售时间    数量 

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'  , 30



DECLARE @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) T



SET @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 行受影响)

*/