关于表列更换为主轴的问题续
问题是这样的:
表T1,字段有A,B,日期, 花费, 次数
A B 日期 花费 次数
-----------------------------------------------
1 配件 2006-12-16 2 1
1 配件 2006-12-17 4 2
1 配件 2006-12-18 16 1
1 配件 2006-12-19 2 1
1 配件 2006-12-20 16 1
2 主件 2006-12-16 4 2
2 主件 2006-12-17 3 3
2 主件 2006-12-18 5 4
2 主件 2006-12-19 0 0
2 主件 2006-12-20 4 1
3 副件1 2006-12-16 4 1
3 副件1 2006-12-17 3 1
7 副件5 2006-12-16 5 1
7 副件5 2006-12-18 4 1
现在的要求是:
通过一个查询把日期添加到主轴,
字段有A,B,统计方式, 日期1,日期2,日期3... ,日期中存放的是花费.
A B 统计方式 2006-12-16 2006-12-17 2006-12-18 总计
-----------------------------------------------------------
1 配件 花费 2 4 16 22
次数 1 2 1 4
2 主件 花费 4 3 5 13
次数 2 3 4 9
3 副件1 花费 4 3 0 7
次数 1 1 0 2
7 副件5 花费 5 4 0 9
次数 1 1 0 2
现在不知道这个查询怎么写,多谢大家了!
问题是这样的:
表T1,字段有A,B,日期, 花费, 次数
A B 日期 花费 次数
-----------------------------------------------
1 配件 2006-12-16 2 1
1 配件 2006-12-17 4 2
1 配件 2006-12-18 16 1
1 配件 2006-12-19 2 1
1 配件 2006-12-20 16 1
2 主件 2006-12-16 4 2
2 主件 2006-12-17 3 3
2 主件 2006-12-18 5 4
2 主件 2006-12-19 0 0
2 主件 2006-12-20 4 1
3 副件1 2006-12-16 4 1
3 副件1 2006-12-17 3 1
7 副件5 2006-12-16 5 1
7 副件5 2006-12-18 4 1
现在的要求是:
通过一个查询把日期添加到主轴,
字段有A,B,统计方式, 日期1,日期2,日期3... ,日期中存放的是花费.
A B 统计方式 2006-12-16 2006-12-17 2006-12-18 总计
-----------------------------------------------------------
1 配件 花费 2 4 16 22
次数 1 2 1 4
2 主件 花费 4 3 5 13
次数 2 3 4 9
3 副件1 花费 4 3 0 7
次数 1 1 0 2
7 副件5 花费 5 4 0 9
次数 1 1 0 2
现在不知道这个查询怎么写,多谢大家了!
--如果日期为DATETIME型.
if object_id('pubs..tb') is not null
drop table tb
go
create table tb(A int,B varchar(10),日期 datetime,花费 int)
insert into tb(A,B,日期,花费) values(1, '配件' ,'2006-12-16' ,2)
insert into tb(A,B,日期,花费) values(1, '配件' ,'2006-12-17' ,4)
insert into tb(A,B,日期,花费) values(1, '配件' ,'2006-12-18' ,16)
insert into tb(A,B,日期,花费) values(1, '配件' ,'2006-12-19' ,2)
insert into tb(A,B,日期,花费) values(1, '配件' ,'2006-12-20' ,16)
insert into tb(A,B,日期,花费) values(2, '主件' ,'2006-12-16' ,4)
insert into tb(A,B,日期,花费) values(2, '主件' ,'2006-12-17' ,3)
insert into tb(A,B,日期,花费) values(2, '主件' ,'2006-12-18' ,5)
insert into tb(A,B,日期,花费) values(2, '主件' ,'2006-12-19' ,0)
insert into tb(A,B,日期,花费) values(2, '主件' ,'2006-12-20' ,4)
insert into tb(A,B,日期,花费) values(3, '副件1', '2006-12-16', 4)
insert into tb(A,B,日期,花费) values(3, '副件1', '2006-12-17', 3)
insert into tb(A,B,日期,花费) values(7, '副件5', '2006-12-16', 5)
insert into tb(A,B,日期,花费) values(7, '副件5', '2006-12-18', 4)
go
declare @sql varchar(8000)
set @sql = 'select a,b'
select @sql = @sql + ' , sum(case 日期 when ''' + convert(varchar(10),日期,120) + ''' then 花费 else 0 end) [' + convert(varchar(10),日期,120) + ']'
from (select distinct convert(varchar(10),日期,120) 日期 from tb) as a
set @sql = @sql + ' from tb group by a,b order by a,b'
exec(@sql)
/*
a b 2006-12-16 2006-12-17 2006-12-18 2006-12-19 2006-12-20
----------- ---------- ----------- ----------- ----------- ----------- -----------
1 配件 2 4 16 2 16
2 主件 4 3 5 0 4
3 副件1 4 3 0 0 0
7 副件5 5 0 4 0 0
*/
drop table tb
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = N'Select A, B, N''花费'' As 统计方式', @S2 = N' Union All Select A, B, N''次数'''
Select @S1 = @S1 + N', SUM(Case 日期 When ''' + 日期 + N''' Then 花费 Else 0 End) As [' + 日期 + ']',
@S2 = @S2 + N', SUM(Case 日期 When ''' + 日期 + N''' Then 次数 Else 0 End) As [' + 日期 + ']'
From T1 Group By 日期
Select @S1 = @S1 + N',SUM(花费) As 总计 From T1 Group By A, B', @S2 = @S2 + N',SUM(次数) As 总计 From T1 Group By A, B Order By A, B, 统计方式 Desc'
Print @S1 + @S2
EXEC(@S1 + @S2)
(A Int,
B Nvarchar(10),
日期 Varchar(10),
花费 Int,
次数 Int)Insert T1 Select 1, N'配件', '2006-12-16', 2, 1
Union All Select 1, N'配件', '2006-12-17', 4, 2
Union All Select 1, N'配件', '2006-12-18', 16, 1
Union All Select 1, N'配件', '2006-12-19', 2, 1
Union All Select 1, N'配件', '2006-12-20', 16, 1
Union All Select 2, N'主件', '2006-12-16', 4, 2
Union All Select 2, N'主件', '2006-12-17', 3, 3
Union All Select 2, N'主件', '2006-12-18', 5, 4
Union All Select 2, N'主件', '2006-12-19', 0, 0
Union All Select 2, N'主件', '2006-12-20', 4, 1
Union All Select 3, N'副件1', '2006-12-16', 4, 1
Union All Select 3, N'副件1', '2006-12-17', 3, 1
Union All Select 7, N'副件5', '2006-12-16', 5, 1
Union All Select 7, N'副件5', '2006-12-18', 4, 1
GO
--如果日期是字符型
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = N'Select A, B, N''花费'' As 统计方式', @S2 = N' Union All Select A, B, N''次数'''
Select @S1 = @S1 + N', SUM(Case 日期 When ''' + 日期 + N''' Then 花费 Else 0 End) As [' + 日期 + ']',
@S2 = @S2 + N', SUM(Case 日期 When ''' + 日期 + N''' Then 次数 Else 0 End) As [' + 日期 + ']'
From T1 Group By 日期
Select @S1 = @S1 + N',SUM(花费) As 总计 From T1 Group By A, B', @S2 = @S2 + N',SUM(次数) As 总计 From T1 Group By A, B Order By A, B, 统计方式 Desc'
Print @S1 + @S2
EXEC(@S1 + @S2)
GO
Drop Table T1
--Result
/*
A B 统计方式 2006-12-16 2006-12-17 2006-12-18 2006-12-19 2006-12-20 总计
1 配件 花费 2 4 16 2 16 40
1 配件 次数 1 2 1 1 1 6
2 主件 花费 4 3 5 0 4 16
2 主件 次数 2 3 4 0 1 10
3 副件1 花费 4 3 0 0 0 7
3 副件1 次数 1 1 0 0 0 2
7 副件5 花费 5 0 4 0 0 9
7 副件5 次数 1 0 1 0 0 2
*/
insert ta select 1, '配件', '2006-12-16', 2,1
insert ta select 1, '配件', '2006-12-17', 4,2
insert ta select 1, '配件', '2006-12-18', 16,1
insert ta select 1, '配件', '2006-12-19', 2,1
insert ta select 1, '配件', '2006-12-20', 16,1
insert ta select 2, '主件', '2006-12-16', 4,2
insert ta select 2, '主件', '2006-12-17', 3,3
insert ta select 2, '主件', '2006-12-18', 5,4
insert ta select 2, '主件', '2006-12-19', 0,0
insert ta select 2, '主件', '2006-12-20', 4,1
insert ta select 3, '副件1', '2006-12-16', 4,1
insert ta select 3, '副件1', '2006-12-17', 3,1
insert ta select 7, '副件5', '2006-12-16', 5,1
insert ta select 7, '副件5', '2006-12-18', 4,1declare @sql varchar(4000),@sql2 varchar(4000)
select @sql='',@sql2=''
select @sql=@sql+','+quotename(date)+'=sum(case convert(varchar(10),日期,120) when '+quotename(date,'''')
+' then [花费] else 0 end)'
from (select distinct convert(varchar(10),日期,120) as date from ta)t
--print @sql
set @sql='select a,b,[统计方式]=''花费'''+@sql+' from ta group by a,b'select @sql2=@sql2+','+quotename(date)+'=sum(case convert(varchar(10),日期,120) when '+quotename(date,'''')
+' then [次数] else 0 end)'
from (select distinct convert(varchar(10),日期,120) as date from ta)t set @sql2=' union all select a,b,[统计方式]=''次数'''+@sql2+' from ta group by a,b order by a,b,统计方式'exec(@sql+@sql2)a b 统计方式 2006-12-16 2006-12-17 2006-12-18 2006-12-19 2006-12-20
----------- -------------------------------------------------- ---- ----------- ----------- ----------- ----------- -----------
1 配件 次数 1 2 1 1 1
1 配件 花费 2 4 16 2 16
2 主件 次数 2 3 4 0 1
2 主件 花费 4 3 5 0 4
3 副件1 次数 1 1 0 0 0
3 副件1 花费 4 3 0 0 0
7 副件5 次数 1 0 1 0 0
7 副件5 花费 5 0 4 0 0(8 行受影响)
(A Int,
B Nvarchar(10),
日期 DateTime,
花费 Int,
次数 Int)Insert T1 Select 1, N'配件', '2006-12-16', 2, 1
Union All Select 1, N'配件', '2006-12-17', 4, 2
Union All Select 1, N'配件', '2006-12-18', 16, 1
Union All Select 1, N'配件', '2006-12-19', 2, 1
Union All Select 1, N'配件', '2006-12-20', 16, 1
Union All Select 2, N'主件', '2006-12-16', 4, 2
Union All Select 2, N'主件', '2006-12-17', 3, 3
Union All Select 2, N'主件', '2006-12-18', 5, 4
Union All Select 2, N'主件', '2006-12-19', 0, 0
Union All Select 2, N'主件', '2006-12-20', 4, 1
Union All Select 3, N'副件1', '2006-12-16', 4, 1
Union All Select 3, N'副件1', '2006-12-17', 3, 1
Union All Select 7, N'副件5', '2006-12-16', 5, 1
Union All Select 7, N'副件5', '2006-12-18', 4, 1
GO
--如果日期是字符型
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = N'Select A, B, N''花费'' As 统计方式', @S2 = N' Union All Select A, B, N''次数'''
Select @S1 = @S1 + N', SUM(Case Convert(Varchar(10), 日期, 120) When ''' + 日期 + N''' Then 花费 Else 0 End) As [' + 日期 + ']',
@S2 = @S2 + N', SUM(Case Convert(Varchar(10), 日期, 120) When ''' + 日期 + N''' Then 次数 Else 0 End) As [' + 日期 + ']'
From (Select Distinct Convert(Varchar(10), 日期, 120) As 日期 From T1) A Order By 日期
Select @S1 = @S1 + N',SUM(花费) As 总计 From T1 Group By A, B', @S2 = @S2 + N',SUM(次数) As 总计 From T1 Group By A, B Order By A, B, 统计方式 Desc'
Print @S1 + @S2
EXEC(@S1 + @S2)
GO
Drop Table T1
--Result
/*
A B 统计方式 2006-12-16 2006-12-17 2006-12-18 2006-12-19 2006-12-20 总计
1 配件 花费 2 4 16 2 16 40
1 配件 次数 1 2 1 1 1 6
2 主件 花费 4 3 5 0 4 16
2 主件 次数 2 3 4 0 1 10
3 副件1 花费 4 3 0 0 0 7
3 副件1 次数 1 1 0 0 0 2
7 副件5 花费 5 0 4 0 0 9
7 副件5 次数 1 0 1 0 0 2
*/
insert ta select 1, '配件', '2006-12-16', 2,1
insert ta select 1, '配件', '2006-12-17', 4,2
insert ta select 1, '配件', '2006-12-18', 16,1
insert ta select 1, '配件', '2006-12-19', 2,1
insert ta select 1, '配件', '2006-12-20', 16,1
insert ta select 2, '主件', '2006-12-16', 4,2
insert ta select 2, '主件', '2006-12-17', 3,3
insert ta select 2, '主件', '2006-12-18', 5,4
insert ta select 2, '主件', '2006-12-19', 0,0
insert ta select 2, '主件', '2006-12-20', 4,1
insert ta select 3, '副件1', '2006-12-16', 4,1
insert ta select 3, '副件1', '2006-12-17', 3,1
insert ta select 7, '副件5', '2006-12-16', 5,1
insert ta select 7, '副件5', '2006-12-18', 4,1declare @sql varchar(4000),@sql2 varchar(4000)
select @sql='',@sql2=''
select @sql=@sql+','+quotename(date)+'=sum(case convert(varchar(10),日期,120) when '+quotename(date,'''')
+' then [花费] else 0 end)'
from (select distinct convert(varchar(10),日期,120) as date from ta)t
--print @sql
set @sql='select a,b,[统计方式]=''花费'''+@sql+' from ta group by a,b'select @sql2=@sql2+','+quotename(date)+'=sum(case convert(varchar(10),日期,120) when '+quotename(date,'''')
+' then [次数] else 0 end)'
from (select distinct convert(varchar(10),日期,120) as date from ta)t set @sql2=' union all select a,b,[统计方式]=''次数'''+@sql2+' from ta group by a,b order by a asc,b asc,统计方式 desc'--在这里改排序exec(@sql+@sql2)
a b 统计方式 2006-12-16 2006-12-17 2006-12-18 2006-12-19 2006-12-20
----------- -------------------------------------------------- ---- ----------- ----------- ----------- ----------- -----------
1 配件 花费 2 4 16 2 16
1 配件 次数 1 2 1 1 1
2 主件 花费 4 3 5 0 4
2 主件 次数 2 3 4 0 1
3 副件1 花费 4 3 0 0 0
3 副件1 次数 1 1 0 0 0
7 副件5 花费 5 0 4 0 0
7 副件5 次数 1 0 1 0 0(8 行受影响)
create table ta(A int,B varchar(50),日期 datetime, 花费 int,次数 int)
insert ta select 1, '配件', '2006-12-16', 2,1
insert ta select 1, '配件', '2006-12-17', 4,2
insert ta select 1, '配件', '2006-12-18', 16,1
insert ta select 1, '配件', '2006-12-19', 2,1
insert ta select 1, '配件', '2006-12-20', 16,1
insert ta select 2, '主件', '2006-12-16', 4,2
insert ta select 2, '主件', '2006-12-17', 3,3
insert ta select 2, '主件', '2006-12-18', 5,4
insert ta select 2, '主件', '2006-12-19', 0,0
insert ta select 2, '主件', '2006-12-20', 4,1
insert ta select 3, '副件1', '2006-12-16', 4,1
insert ta select 3, '副件1', '2006-12-17', 3,1
insert ta select 7, '副件5', '2006-12-16', 5,1
insert ta select 7, '副件5', '2006-12-18', 4,1
declare @sql varchar(4000),@sql2 varchar(4000)
select @sql='',@sql2=''
select @sql=@sql+','+quotename(date)+'=sum(case convert(varchar(10),日期,120) when '+quotename(date,'''')
+' then [花费] else 0 end)'
from (select distinct convert(varchar(10),日期,120) as date from ta)t
--print @sql
set @sql='select a,b,[统计方式]=''花费'''+@sql+',sum(花费) as [合计] from ta group by a,b'select @sql2=@sql2+','+quotename(date)+'=sum(case convert(varchar(10),日期,120) when '+quotename(date,'''')
+' then [次数] else 0 end)'
from (select distinct convert(varchar(10),日期,120) as date from ta)t set @sql2=' union all select a,b,[统计方式]=''次数'''+@sql2+',sum(次数) as [合计] from ta group by a,b order by a asc,b asc,统计方式 desc'--在这里改排序exec(@sql+@sql2)a b 统计方式 2006-12-16 2006-12-17 2006-12-18 2006-12-19 2006-12-20 合计
----------- -------------------------------------------------- ---- ----------- ----------- ----------- ----------- ----------- -----------
1 配件 花费 2 4 16 2 16 40
1 配件 次数 1 2 1 1 1 6
2 主件 花费 4 3 5 0 4 16
2 主件 次数 2 3 4 0 1 10
3 副件1 花费 4 3 0 0 0 7
3 副件1 次数 1 1 0 0 0 2
7 副件5 花费 5 0 4 0 0 9
7 副件5 次数 1 0 1 0 0 2(8 行受影响)