分列显示数据

[oldmoney01]

数据在数据库里是这样的：
YEAR NUM
1992 200
1993 300
1994 270
...
2008 800
现在，我想要这样的或查询结果：
1992 200 1993 300 1994 270
...
即要分3列显示出来，该怎么实现啊？

[liangCK]

行转列？

[liangCK]

产品编号     产品名称   销售时间    数量 
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 行受影响)
*/

[dawugui]

create table tb([YEAR] int,NUM int)
insert into tb values(1992,     200) 
insert into tb values(1993,     300) 
insert into tb values(1994,     270) 
go

declare @output varchar(8000)
select @output = coalesce(@output + ',' , '') + cast([year] as varchar) + ',' + cast(num as varchar) from tb
print @output

drop table tb

/*
1992,200,1993,300,1994,270
*/

[dawugui]

create table tb([YEAR] int,NUM int)
insert into tb values(1992,     200) 
insert into tb values(1993,     300) 
insert into tb values(1994,     270) 
go

declare @output varchar(8000)
select @output = coalesce(@output + ' ' , '') + cast([year] as varchar) + ' ' + cast(num as varchar) from tb
print @output

drop table tb

/*
1992 200 1993 300 1994 270
*/

[oldmoney01]

To 4楼：
我要的是只分三列显示，如果按你的方法，那就成1行显示了
1992 200 1993 300 1994 270 1995 8000 1996 900
我要的是：
1992 200 1993 300 1994 270
1995 8000 1996 900 ...

[dawugui]

这个是分两列的,使用整除和取模的办法.

请写SQL语句将下表test1
person ttime Iotype
001 8:00 1
001 12:00 2
001 13:00 1
001 17:00 2
002 8:00 1
002 12:00 2

转换成表test2
Wpid person in Out
1 001 8:00 12:00
2 001 13:00 17:00
3 002 8:00 12:00
最好使用一句SQL语句搞顶(不要使用变量或函数,游标之类),,,不胜感激

--------------------------------------------------------------------
--我将数据略做修改,8:00改为08:00
--使用sql server 2005的row_number函数可以直接得到前面的序,sql server 2000要麻烦一些.
create table tb (person varchar(10), ttime varchar(10),Iotype int)
insert into tb
select '001' ,'08:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00',1 union all
select '002' ,'12:00',2

select Wpid = row_number() over(order by m.person , m.ttime) , m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1

drop table tb

/*
Wpid person in out
-------------------- ---------- ---------- ----------
1 001 08:00 12:00
2 001 13:00 17:00
3 002 08:00 12:00

(3 行受影响)
*/

-----------------------------------------------------------------------------------------

--sql server 2000中的写法.
create table tb (person varchar(10), ttime varchar(10),Iotype int)
insert into tb
select '001' ,'08:00',1 union all
select '001' ,'12:00',2 union all
select '001' ,'13:00',1 union all
select '001' ,'17:00',2 union all
select '002' ,'08:00',1 union all
select '002' ,'12:00',2

SELECT Wpid=(SELECT COUNT(1) FROM
(
select m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1
) t1 WHERE (person < t2.person) or (person = t2.person and [in] < t2.[in])) + 1 , * from
(
select m.person , m.ttime [in], n.ttime [out] from
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) m,
(select px = (select count(*) from tb where person = a.person and ttime < a.ttime) + 1 , * from tb a) n
where m.person = n.person and m.px = n.px - 1 and m.px%2 = 1
) t2
drop table tb

/*
Wpid person in out
-------------------- ---------- ---------- ----------
1 001 08:00 12:00
2 001 13:00 17:00
3 002 08:00 12:00

(3 行受影响)
*/

[dawugui]

--如果能按year区分每行的大小,可如下操作,否则需要使用临时表,见6楼.

create table tb([year] int,NUM int)
insert into tb values(1992,     200) 
insert into tb values(1993,     300) 
insert into tb values(1994,     270) 
insert into tb values(1995,     200) 
insert into tb values(1996,     300) 
insert into tb values(1997,     270) 
go

select 
  max(case (px-1)%3 when 0 then [year] else 0 end) year1,
  max(case (px-1)%3 when 0 then [num] else 0 end) num1,
  max(case (px-1)%3 when 1 then [year] else 0 end) year2,
  max(case (px-1)%3 when 1 then [num] else 0 end) num2,
  max(case (px-1)%3 when 2 then [year] else 0 end) year3,
  max(case (px-1)%3 when 2 then [num] else 0 end) num3
from
(
  select * , px = (select count(1) from tb where [year] < t.[year]) + 1 from tb t
) m
group by (px-1)/3

drop table tb

/*
year1       num1        year2       num2        year3       num3        
----------- ----------- ----------- ----------- ----------- ----------- 
1992        200         1993        300         1994        270
1995        200         1996        300         1997        270

（所影响的行数为 2 行）
*/

[dawugui]

create table tb([year] int,NUM int)
insert into tb values(1992,     200) 
insert into tb values(1993,     300) 
insert into tb values(1994,     270) 
insert into tb values(1995,     200) 
insert into tb values(1996,     300) 
insert into tb values(1997,     270) 
go

--如果能按year区分每行的大小,可如下操作.
select 
  max(case (px-1)%3 when 0 then [year] else 0 end) year1,
  max(case (px-1)%3 when 0 then [num] else 0 end) num1,
  max(case (px-1)%3 when 1 then [year] else 0 end) year2,
  max(case (px-1)%3 when 1 then [num] else 0 end) num2,
  max(case (px-1)%3 when 2 then [year] else 0 end) year3,
  max(case (px-1)%3 when 2 then [num] else 0 end) num3
from
(
  select * , px = (select count(1) from tb where [year] < t.[year]) + 1 from tb t
) m
group by (px-1)/3

--如果不能区分大小，则需要使用临时表
select * , px = identity(int,1,1) into tmp from tb
select 
  max(case (px-1)%3 when 0 then [year] else 0 end) year1,
  max(case (px-1)%3 when 0 then [num] else 0 end) num1,
  max(case (px-1)%3 when 1 then [year] else 0 end) year2,
  max(case (px-1)%3 when 1 then [num] else 0 end) num2,
  max(case (px-1)%3 when 2 then [year] else 0 end) year3,
  max(case (px-1)%3 when 2 then [num] else 0 end) num3
from tmp
group by (px-1)/3


drop table tb,tmp

/*
year1       num1        year2       num2        year3       num3        
----------- ----------- ----------- ----------- ----------- ----------- 
1992        200         1993        300         1994        270
1995        200         1996        300         1997        270

（所影响的行数为 2 行）
*/

[bqb]

--不用临时表可以这样：

create table tb(year varchar(4),num int)
insert into tb 
select '1992',200 union all
select '1993', 300 union all
select '1994', 270 union all 
select '1995', 8000 union all
select '1996', 900

select 	year1=t1.year,num1=t1.num,
	year2=t2.year,num2=t2.num,
	year3=t3.year,num3=t3.num 
from 	  (select  year,num,id=(select count(1) from tb where year<=a.year) from tb a) t1 
left join (select  year,num,id=(select count(1) from tb where year<=a.year) from tb a) t2 on t2.id=t1.id+1
left join (select  year,num,id=(select count(1) from tb where year<=a.year) from tb a) t3 on t3.id=t1.id+2
where t1.year%3=0


/*
year1	num1	year2	num2	year3	num3 
-----------------------------------------------
1992	200	1993	300	1994	270
1995	8000	1996	900	NULL	NULL
*/

drop table tb