关于行列转换的问题

[zdc198771]

SQL数据转换的问题
表如下
1 1123 存 2010-09-01
4 24521 取 2010-09-01
5 12345 取 2010-09-02
6 1232 存 2010-09-02
7 78525 存 2010-09-02
8 78721 取 2010-09-03
9 27752 存 2010-09-04
10 7273 存 2010-09-04
11 42272 存 2010-09-04
12 25722 取 2010-09-05
13 2751 存 2010-09-05
14 37253 取 2010-09-06
15 5436 存 2010-09-06
16 28727 存 2010-09-07
17 25777 取 2010-09-07
18 111 存 2010-09-07

结果1
2010-09-01 1123 24521
2010-09-02 79757 12345
2010-09-03 0 78721
2010-09-04 77297 0
2010-09-05 2751 25722
2010-09-06 5436 37253
2010-09-07 28838 25777

结果2
2010-09-01 1123 24521 -23398 -23398
2010-09-02 79757 12345 67412 44014
2010-09-03 0 78721 -78721 -34707
2010-09-04 77297 0 77297 42590
2010-09-05 2751 25722 -22971 19619
2010-09-06 5436 37253 -31817 -12198
2010-09-07 28838 25777 3061 -9137

结果3
存 1123 79757 NULL 77297 2751 5436 28838
取 24521 12345 78721 NULL 25722 37253 25777

结果4
存 1123 78525 NULL 42272 2751 5436 28727
取 24521 12345 78721 NULL 25722 37253 25777
库存 -23398 67412 -78721 77297 -22971 -31817 3061

怎么查出这样的结果啊.....来个大大教教

[ws_hgo]

我只列了部分数据

其余的找着这个写

if object_id('table1') is not null drop table table1
create table table1
(
  id int identity(1,1) primary key,
  Num int,
  status varchar(20),
  date datetime
)
insert into table1 select 1123,'存','2010-09-01'
union all select 24521,'取','2010-09-01'
union all select 12345,'取','2010-09-02'
union all select 1232,'存','2010-09-02'
union all select 78525,'存','2010-09-02'
union all select 78721,'取','2010-09-03'
union all select 27752,'存','2010-09-04'


select convert(varchar(10),date,120) 日期,
       sum(case when status='存' then Num else 0 end) '存',
       sum(case when status='取' then Num else 0 end) '取'
from table1
group by convert(varchar(10),date,120)

日期         存           取
---------- ----------- -----------
2010-09-01 1123        24521
2010-09-02 79757       12345
2010-09-03 0           78721
2010-09-04 27752       0

(4 行受影响)

[zdc198771]

恩,谢谢楼上的.后面3个也教教我吧?是一样的方法么?

[ws_hgo]

[Quote=引用 2 楼 zdc198771 的回复:]
恩,谢谢楼上的.后面3个也教教我吧?是一样的方法么?
[/Quote]
是的
照着写就行啦
主要是
case when then else end的用法

[zdc198771]

可是结果2的第4列是每天库存啊,今天+昨天的,我只能固定加上一个值.
这个怎么办.....帮帮忙吧....

[东那个升]

--create table #test(id int,num int,type varchar(10),date varchar(10))
--insert #test select 1 ,1123 ,'存','2010-09-01'
--insert #test select 4 ,24521 ,'取','2010-09-01'
--insert #test select 5 ,12345 ,'取','2010-09-02'
--insert #test select 6 ,1232 ,'存','2010-09-02'
--insert #test select 7 ,78525 ,'存','2010-09-02'
--insert #test select 8 ,78721 ,'取','2010-09-03'
--insert #test select 9 ,27752 ,'存','2010-09-04'
--insert #test select 10, 7273 ,'存','2010-09-04'
--insert #test select 11 ,42272 ,'存','2010-09-04'
--insert #test select 12 ,25722 ,'取','2010-09-05'
--insert #test select 13 ,2751 ,'存','2010-09-05'
--insert #test select 14 ,37253 ,'取','2010-09-06'
--insert #test select 15 ,5436 ,'存','2010-09-06'
--insert #test select 16 ,28727 ,'存','2010-09-07'
--insert #test select 17 ,25777 ,'取','2010-09-07'
--insert #test select 18 ,111 ,'存','2010-09-07'




select date,
       sum(case when type='存' then Num else 0 end) '存',
       sum(case when type='取' then Num else 0 end) '取'
from #test
group by date

;with cte as(
select date,
       sum(case when type='存' then Num else 0 end) '存',
       sum(case when type='取' then Num else 0 end) '取',
       sum(case when type='存' then Num else 0 end) -sum(case when type='取' then Num else 0 end)  as YE
from #test
group by date)
select *,isnull(YE+(select SUM(ISNULL(YE,0)) from cte where a.date>date ),YE) from cte  a


declare @col varchar(100)
set  @col=replace((select quotename(convert(varchar(10),date,120))  as 'data()'from  #test group by date
					for xml path('')),' ',',')
exec('
select type,'+@col+' from (SELECT num,type,date from #test) a
pivot( sum(num) for date in('+@col+')) as pvt')

exec('
select type,'+@col+' 
		from (SELECT sum(case when type=''存'' then Num else 0 end) -sum(case when type=''取'' then Num else 0 end) as num
		,''库存'' as type,date from #test
		group by date
		union all
		SELECT num,type,date from #test) a
pivot( sum(num) for date in('+@col+')) as pvt')


/*
date       存           取
---------- ----------- -----------
2010-09-01 1123        24521
2010-09-02 79757       12345
2010-09-03 0           78721
2010-09-04 77297       0
2010-09-05 2751        25722
2010-09-06 5436        37253
2010-09-07 28838       25777

(7 行受影响)

date       存           取           YE          
---------- ----------- ----------- ----------- -----------
2010-09-01 1123        24521       -23398      -23398
2010-09-02 79757       12345       67412       44014
2010-09-03 0           78721       -78721      -34707
2010-09-04 77297       0           77297       42590
2010-09-05 2751        25722       -22971      19619
2010-09-06 5436        37253       -31817      -12198
2010-09-07 28838       25777       3061        -9137

(7 行受影响)

type       2010-09-01  2010-09-02  2010-09-03  2010-09-04  2010-09-05  2010-09-06  2010-09-07
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
存          1123        79757       NULL        77297       2751        5436        28838
取          24521       12345       78721       NULL        25722       37253       25777

(2 行受影响)

type       2010-09-01  2010-09-02  2010-09-03  2010-09-04  2010-09-05  2010-09-06  2010-09-07
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
存          1123        79757       NULL        77297       2751        5436        28838
库存         -23398      67412       -78721      77297       -22971      -31817      3061
取          24521       12345       78721       NULL        25722       37253       25777

(3 行受影响)
*/

[喜-喜]

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
ID int,
金额 int,
操作 char(3),
日期 datetime
)
go
--插入测试数据
insert into tb select 1,1123,'存','2010-09-01'
union all select 4,24521,'取','2010-09-01'
union all select 5,12345,'取','2010-09-02'
union all select 6,1232,'存','2010-09-02'
union all select 7,78525,'存','2010-09-02'
union all select 8,78721,'取','2010-09-03'
union all select 9,27752,'存','2010-09-04'
union all select 10,7273,'存','2010-09-04'
union all select 11,42272,'存','2010-09-04'
union all select 12,25722,'取','2010-09-05'
union all select 13,2751,'存','2010-09-05'
union all select 14,37253,'取','2010-09-06'
union all select 15,5436,'存','2010-09-06'
union all select 16,28727,'存','2010-09-07'
union all select 17,25777,'取','2010-09-07'
union all select 18,111,'存','2010-09-07'
go
--代码实现

select [日期]=convert(varchar(10),[日期],120)
,[存]=sum(case when [操作]='存' then [金额] else 0 end)
,[取]=sum(case when [操作]='取' then [金额] else 0 end)
from tb group by [日期]

/*测试结果

日期          存     取
-------------------------------
2010-09-01	1123	24521
2010-09-02	79757	12345
2010-09-03	0	78721
2010-09-04	77297	0
2010-09-05	2751	25722
2010-09-06	5436	37253
2010-09-07	28838	25777

(7 行受影响)
*/

;with t as(
select idd=row_number()over(order by [日期])
,[日期]=convert(varchar(10),[日期],120)
,[存]=sum(case when [操作]='存' then [金额] else 0 end)
,[取]=sum(case when [操作]='取' then [金额] else 0 end)
,[库存1]=sum(case when [操作]='存' then [金额] else 0 end)-sum(case when [操作]='取' then [金额] else 0 end)
from tb group by [日期]
)
,tt as(
	select *,[库存2]=[库存1] from t where idd=1
	union all
	select t.*,[库存2]=t.[库存1]+tt.[库存2] from t,tt where t.idd=tt.idd+1
)
select * from tt

/*测试结果
 
日期          存       取       库存1     库存2
-------------------------------------------------
2010-09-01	1123	24521	-23398	-23398
2010-09-02	79757	12345	67412	44014
2010-09-03	0	78721	-78721	-34707
2010-09-04	77297	0	77297	42590
2010-09-05	2751	25722	-22971	19619
2010-09-06	5436	37253	-31817	-12198
2010-09-07	28838	25777	3061	-9137

(7 行受影响)
*/

[zdc198771]

谢谢楼上的各位啦,很受用,我还是要研究研究