更正: select 客户,min(日期),total=sum(销售数量) from t group by 客户,left(日期,4)
--生成测试示例数据 create table t(日期 varchar(100), 客户 varchar(100),销售数量 int) insert into t select '951002-001','家福中清', 200 insert into t select '951002-001','家福中清', 300 insert into t select '951102-001','家福中清', 400 insert into t select '951002-001','家福新店', 500 insert into t select '951002-001','家福新店', 600 insert into t select '951102-001','家福新店', 700 insert into t select '951002-001','家福愛河', 800 insert into t select '951002-001','家福愛河', 900 insert into t select '951102-001','家福愛河', 1000 go--生成辅助表 select 客户,日期=min(left(日期,4)),销售数量=sum(销售数量) into # from t group by 客户,left(日期,4)--创建动态SQL declare @sql varchar(8000) set @sql=''select @sql=@sql+',['+rtrim(num)+']=isnull(max(case num when '+rtrim(num)+' then 销售数量 end),0)' from (select 日期 as Num from # a) b group by num order by numset @sql='select 客户'+@sql+' from (select a.*,日期 as Num from # a) b group by b.客户'exec(@sql) go--删除测试环境 drop table t drop table # go--结果 /* 客户 9510 9511 ---------------------------- 家福愛河 1700 1000 家福新店 1100 700 家福中清 500 400 */
--不用辅助表可以这样 --生成测试示例数据 create table t(日期 varchar(100), 客户 varchar(100),销售数量 int) insert into t select '951002-001','家福中清', 200 insert into t select '951002-001','家福中清', 300 insert into t select '951102-001','家福中清', 400 insert into t select '951002-001','家福新店', 500 insert into t select '951002-001','家福新店', 600 insert into t select '951102-001','家福新店', 700 insert into t select '951002-001','家福愛河', 800 insert into t select '951002-001','家福愛河', 900 insert into t select '951102-001','家福愛河', 1000 go--创建动态SQL declare @sql varchar(8000) set @sql=''select @sql=@sql+',['+rtrim(num)+']=isnull(max(case num when '+rtrim(num)+' then 销售数量 end),0)' from ( select 客户,num=min(left(日期,4)),销售数量=sum(销售数量) from t group by 客户,left(日期,4) ) b group by num order by numset @sql='select 客户'+@sql+' from ( select 客户,num=min(left(日期,4)),销售数量=sum(销售数量) from t group by 客户,left(日期,4) ) b group by b.客户'exec(@sql) go--删除测试环境 drop table t drop table # go--结果 /* 客户 9510 9511 ---------------------------- 家福愛河 1700 1000 家福新店 1100 700 家福中清 500 400 */
--不用辅助表可以这样 --生成测试示例数据 create table t(日期 varchar(100), 客户 varchar(100),销售数量 int) insert into t select '951002-001','家福中清', 200 insert into t select '951002-001','家福中清', 300 insert into t select '951102-001','家福中清', 400 insert into t select '951002-001','家福新店', 500 insert into t select '951002-001','家福新店', 600 insert into t select '951102-001','家福新店', 700 insert into t select '951002-001','家福愛河', 800 insert into t select '951002-001','家福愛河', 900 insert into t select '951102-001','家福愛河', 1000 go--创建动态SQL declare @sql varchar(8000) set @sql=''select @sql=@sql+',['+rtrim(num)+']=isnull(max(case num when '+rtrim(num)+' then 销售数量 end),0)' from ( select 客户,num=min(left(日期,4)),销售数量=sum(销售数量) from t group by 客户,left(日期,4) ) b group by num order by numset @sql='select 客户'+@sql+' from ( select 客户,num=min(left(日期,4)),销售数量=sum(销售数量) from t group by 客户,left(日期,4) ) b group by b.客户'exec(@sql) go--删除测试环境 drop table t go--结果 /* 客户 9510 9511 ---------------------------- 家福愛河 1700 1000 家福新店 1100 700 家福中清 500 400 */
Declare @SQL varchar(4000) Select @SQL='' Select @SQL=@SQL + ',[' + left(日期,4) + ']=Sum(case left(日期,4) when ' + left(日期,4) + ' then 数量 else 0 END)' From Table1 Group By 客户,日期 Select @SQL='Select 客户' + @SQL + ' From Table1 Group By 客户,日期' Exec(@SQL)
declare @t table([客户] nvarchar(10),[日期] varchar(20),[数量] int) insert into @t select N'家福中清','951002-001', 24 union all select N'家福中清','951002-001', 4 union all select N'家福中清','951102-001', 4 union all select N'家福中清','951102-001', 12 union all select N'家福新店','951002-003 ', 12 union all select N'家福新店','951002-003', 60 union all select N'家福愛河','951102-004', 24 union all select N'家福愛河','951202-004', 36 select * from @t declare @s nvarchar(4000) set @s='' select @s=@s+N',case when [日期]='''+[日期]+''' then sum([数量]) else 0 end as '''+[日期]+'''' from (select distinct [日期] from @t)T set @s=N'select [客户]'+@s+N' from @t group by [客户],[日期]' print @s select [客户],case when [日期]='951002-001' then sum([数量]) else 0 end as '951002-001',case when [日期]='951002-003' then sum([数量]) else 0 end as '951002-003',case when [日期]='951002-003 ' then sum([数量]) else 0 end as '951002-003 ',case when [日期]='951102-001' then sum([数量]) else 0 end as '951102-001',case when [日期]='951102-004' then sum([数量]) else 0 end as '951102-004',case when [日期]='951202-004' then sum([数量]) else 0 end as '951202-004' from @t group by [客户],[日期]exec(@s) 原始数据------- 客户 日期 数量 --------------------------------------- 家福中清 951002-001 24 家福中清 951002-001 4 家福中清 951102-001 4 家福中清 951102-001 12 家福新店 951002-003 12 家福新店 951002-003 60 家福愛河 951102-004 24 家福愛河 951202-004 36 结果: 客户951002-001 951002-003 951102-001 951102-004 951202-004 --------------------------------------------------------------- 家福中清 28 0 0 0 0 家福新店 0 72 0 0 0 家福中清 0 0 16 0 0 家福愛河 0 0 0 24 0 家福愛河 0 0 0 0 36
偶也来棒场: create table #test(日期 varchar(20), 客户 varchar(10),销售数量 int) insert into #test select '951002-001','家福中清', 200 insert into #test select '951002-001','家福中清', 300 insert into #test select '951102-001','家福中清', 400 insert into #test select '951002-001','家福新店', 500 insert into #test select '951002-001','家福新店', 600 insert into #test select '951102-001','家福新店', 700 insert into #test select '951002-001','家福愛河', 800 insert into #test select '951002-001','家福愛河', 900 insert into #test select '951102-001','家福愛河', 1000declare @sql varchar(1000) set @sql='' select @sql=@sql+',['+rtrim(日期)+']=sum(case left(日期,4) when '+rtrim(日期)+' then 销售数量 else 0 end)' from (select 日期=left(日期,4) from #test group by left(日期,4))ta --print @sql set @sql='select 客户'+@sql+' from #test group by 客户' exec(@sql)客户 9510 9511 ---------- ----------- ----------- 家福愛河 1700 1000 家福新店 1100 700 家福中清 500 400
--生成测试示例数据 create table t(日期 varchar(100), 客户 varchar(100),销售数量 int) insert into t select '951002-001','家福中清', 200 insert into t select '951002-001','家福中清', 300 insert into t select '951102-001','家福中清', 400 insert into t select '951002-001','家福新店', 500 insert into t select '951002-001','家福新店', 600 insert into t select '951102-001','家福新店', 700 insert into t select '951002-001','家福愛河', 800 insert into t select '951002-001','家福愛河', 900 insert into t select '951102-001','家福愛河', 1000 goDeclare @SQL varchar(4000) Select @SQL='' Select @SQL=@SQL + ',[' + left(日期,4) + ']=Sum(case left(日期,4) when ''' + left(日期,4) + ''' then 数量 else 0 END)' From t Group By 日期 Select @SQL Select @SQL='Select 客户' + @SQL + ' From t Group By 客户' Exec(@SQL)
上面的语句还有有些问题,换成这样 Declare @SQL varchar(4000) Select @SQL='' Select @SQL=@SQL + ',[' + max(left(日期,4)) + ']=Sum(case left(日期,4) when ''' + max(left(日期,4)) + ''' then 数量 else 0 END)' From t Group By left(日期,4) Select @SQL Select @SQL='Select 客户' + @SQL + ' From t Group By 客户' Exec(@SQL)
行转列 ,可以把该表导出到access,access有这个功能,然后在连接到access中运行语句。再导回sql server
我用的是SQL 2000。。没有行转列。 如果是2005,好象有这个功能了
这个只用一个SQL语句就可以完成了,可以不需要使用存储过程,既然已经结贴了,我就不多说了!
算了不买关子,让其他朋友也能学到东西,我写出查询: select 客户,sum(case left(日期,4)when '9510' then 销售数量 else 0 end) as '9510' ,sum(case left(日期,4)when '9511' then 销售数量 else 0 end) as '9511' ,sum(case left(日期,4)when '9512' then 销售数量 else 0 end) as '9512' from 资料表 group by 客户 LZ可以试一下!
select 客户,min(日期),total=sum(销售数量)
from t
group by 客户,left(日期,4)
create table t(日期 varchar(100), 客户 varchar(100),销售数量 int)
insert into t select '951002-001','家福中清', 200
insert into t select '951002-001','家福中清', 300
insert into t select '951102-001','家福中清', 400
insert into t select '951002-001','家福新店', 500
insert into t select '951002-001','家福新店', 600
insert into t select '951102-001','家福新店', 700
insert into t select '951002-001','家福愛河', 800
insert into t select '951002-001','家福愛河', 900
insert into t select '951102-001','家福愛河', 1000
go--生成辅助表
select 客户,日期=min(left(日期,4)),销售数量=sum(销售数量)
into #
from t
group by 客户,left(日期,4)--创建动态SQL
declare @sql varchar(8000)
set @sql=''select @sql=@sql+',['+rtrim(num)+']=isnull(max(case num when '+rtrim(num)+' then 销售数量 end),0)'
from (select 日期 as Num from # a) b
group by num order by numset @sql='select 客户'+@sql+' from (select a.*,日期 as Num from # a) b group by b.客户'exec(@sql)
go--删除测试环境
drop table t
drop table #
go--结果
/*
客户 9510 9511
----------------------------
家福愛河 1700 1000
家福新店 1100 700
家福中清 500 400
*/
--生成测试示例数据
create table t(日期 varchar(100), 客户 varchar(100),销售数量 int)
insert into t select '951002-001','家福中清', 200
insert into t select '951002-001','家福中清', 300
insert into t select '951102-001','家福中清', 400
insert into t select '951002-001','家福新店', 500
insert into t select '951002-001','家福新店', 600
insert into t select '951102-001','家福新店', 700
insert into t select '951002-001','家福愛河', 800
insert into t select '951002-001','家福愛河', 900
insert into t select '951102-001','家福愛河', 1000
go--创建动态SQL
declare @sql varchar(8000)
set @sql=''select @sql=@sql+',['+rtrim(num)+']=isnull(max(case num when '+rtrim(num)+' then 销售数量 end),0)'
from
(
select 客户,num=min(left(日期,4)),销售数量=sum(销售数量)
from t
group by 客户,left(日期,4)
) b
group by num order by numset @sql='select 客户'+@sql+' from
(
select 客户,num=min(left(日期,4)),销售数量=sum(销售数量)
from t
group by 客户,left(日期,4)
) b group by b.客户'exec(@sql)
go--删除测试环境
drop table t
drop table #
go--结果
/*
客户 9510 9511
----------------------------
家福愛河 1700 1000
家福新店 1100 700
家福中清 500 400
*/
--生成测试示例数据
create table t(日期 varchar(100), 客户 varchar(100),销售数量 int)
insert into t select '951002-001','家福中清', 200
insert into t select '951002-001','家福中清', 300
insert into t select '951102-001','家福中清', 400
insert into t select '951002-001','家福新店', 500
insert into t select '951002-001','家福新店', 600
insert into t select '951102-001','家福新店', 700
insert into t select '951002-001','家福愛河', 800
insert into t select '951002-001','家福愛河', 900
insert into t select '951102-001','家福愛河', 1000
go--创建动态SQL
declare @sql varchar(8000)
set @sql=''select @sql=@sql+',['+rtrim(num)+']=isnull(max(case num when '+rtrim(num)+' then 销售数量 end),0)'
from
(
select 客户,num=min(left(日期,4)),销售数量=sum(销售数量)
from t
group by 客户,left(日期,4)
) b
group by num order by numset @sql='select 客户'+@sql+' from
(
select 客户,num=min(left(日期,4)),销售数量=sum(销售数量)
from t
group by 客户,left(日期,4)
) b group by b.客户'exec(@sql)
go--删除测试环境
drop table t
go--结果
/*
客户 9510 9511
----------------------------
家福愛河 1700 1000
家福新店 1100 700
家福中清 500 400
*/
Select @SQL=''
Select @SQL=@SQL + ',[' + left(日期,4) + ']=Sum(case left(日期,4) when ' + left(日期,4) + ' then 数量 else 0 END)' From Table1 Group By 客户,日期
Select @SQL='Select 客户' + @SQL + ' From Table1 Group By 客户,日期'
Exec(@SQL)
insert into @t
select N'家福中清','951002-001', 24
union all
select N'家福中清','951002-001', 4
union all
select N'家福中清','951102-001', 4
union all
select N'家福中清','951102-001', 12
union all
select N'家福新店','951002-003 ', 12
union all
select N'家福新店','951002-003', 60
union all
select N'家福愛河','951102-004', 24
union all
select N'家福愛河','951202-004', 36
select * from @t
declare @s nvarchar(4000)
set @s=''
select @s=@s+N',case when [日期]='''+[日期]+''' then sum([数量]) else 0 end as '''+[日期]+''''
from (select distinct [日期] from @t)T
set @s=N'select [客户]'+@s+N' from @t group by [客户],[日期]'
print @s
select [客户],case when [日期]='951002-001' then sum([数量]) else 0 end as '951002-001',case when [日期]='951002-003' then sum([数量]) else 0 end as '951002-003',case when [日期]='951002-003 ' then sum([数量]) else 0 end as '951002-003 ',case when [日期]='951102-001' then sum([数量]) else 0 end as '951102-001',case when [日期]='951102-004' then sum([数量]) else 0 end as '951102-004',case when [日期]='951202-004' then sum([数量]) else 0 end as '951202-004' from @t group by [客户],[日期]exec(@s)
原始数据-------
客户 日期 数量
---------------------------------------
家福中清 951002-001 24
家福中清 951002-001 4
家福中清 951102-001 4
家福中清 951102-001 12
家福新店 951002-003 12
家福新店 951002-003 60
家福愛河 951102-004 24
家福愛河 951202-004 36
结果:
客户951002-001 951002-003 951102-001 951102-004 951202-004
---------------------------------------------------------------
家福中清 28 0 0 0 0
家福新店 0 72 0 0 0
家福中清 0 0 16 0 0
家福愛河 0 0 0 24 0
家福愛河 0 0 0 0 36
create table #test(日期 varchar(20), 客户 varchar(10),销售数量 int)
insert into #test select '951002-001','家福中清', 200
insert into #test select '951002-001','家福中清', 300
insert into #test select '951102-001','家福中清', 400
insert into #test select '951002-001','家福新店', 500
insert into #test select '951002-001','家福新店', 600
insert into #test select '951102-001','家福新店', 700
insert into #test select '951002-001','家福愛河', 800
insert into #test select '951002-001','家福愛河', 900
insert into #test select '951102-001','家福愛河', 1000declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+rtrim(日期)+']=sum(case left(日期,4) when '+rtrim(日期)+' then 销售数量 else 0 end)'
from (select 日期=left(日期,4) from #test group by left(日期,4))ta
--print @sql
set @sql='select 客户'+@sql+' from #test group by 客户'
exec(@sql)客户 9510 9511
---------- ----------- -----------
家福愛河 1700 1000
家福新店 1100 700
家福中清 500 400
create table t(日期 varchar(100), 客户 varchar(100),销售数量 int)
insert into t select '951002-001','家福中清', 200
insert into t select '951002-001','家福中清', 300
insert into t select '951102-001','家福中清', 400
insert into t select '951002-001','家福新店', 500
insert into t select '951002-001','家福新店', 600
insert into t select '951102-001','家福新店', 700
insert into t select '951002-001','家福愛河', 800
insert into t select '951002-001','家福愛河', 900
insert into t select '951102-001','家福愛河', 1000
goDeclare @SQL varchar(4000)
Select @SQL=''
Select @SQL=@SQL + ',[' + left(日期,4) + ']=Sum(case left(日期,4) when ''' + left(日期,4) + ''' then 数量 else 0 END)' From t Group By 日期
Select @SQL
Select @SQL='Select 客户' + @SQL + ' From t Group By 客户'
Exec(@SQL)
Declare @SQL varchar(4000)
Select @SQL=''
Select @SQL=@SQL + ',[' + max(left(日期,4)) + ']=Sum(case left(日期,4) when ''' + max(left(日期,4)) + ''' then 数量 else 0 END)' From t Group By left(日期,4)
Select @SQL
Select @SQL='Select 客户' + @SQL + ' From t Group By 客户'
Exec(@SQL)
如果是2005,好象有这个功能了
select 客户,sum(case left(日期,4)when '9510' then 销售数量 else 0 end) as '9510'
,sum(case left(日期,4)when '9511' then 销售数量 else 0 end) as '9511'
,sum(case left(日期,4)when '9512' then 销售数量 else 0 end) as '9512'
from 资料表 group by 客户
LZ可以试一下!