搜索行转列 于 动态sql参考例子:/*
原表:
name km cj
---------- ---------- -----------
张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78目标表:
姓名 数学 英语 语文 总分
---------- ---------- ----------- ----------- -----------
李四 85 78 78 241
张三 86 75 80 241
*/create table #test (name varchar(10),km varchar(10),cj int)
insert #test select '张三','语文',80
union all select '张三','数学',86
union all select '张三','英语',75
union all select '李四','语文',78
union all select '李四','数学',85
union all select '李四','英语',78
go
select * from #testselect name 姓名,
sum(case km when '语文' then cj end) 语文,
sum(case km when '数学' then cj end) 数学,
sum(case km when '英语' then cj end) 英语,
sum(cj) 总分
from #test group by namedeclare @sql varchar(8000)
set @sql='select name 姓名'
select @sql= @sql+ ',sum(case km when '''+km+''' then cj end) ' + km from (select distinct km from #test) a
set @sql=@sql+',sum(cj) 总分 from #test group by name'
exec (@sql)
go
drop table #test
原表:
name km cj
---------- ---------- -----------
张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78目标表:
姓名 数学 英语 语文 总分
---------- ---------- ----------- ----------- -----------
李四 85 78 78 241
张三 86 75 80 241
*/create table #test (name varchar(10),km varchar(10),cj int)
insert #test select '张三','语文',80
union all select '张三','数学',86
union all select '张三','英语',75
union all select '李四','语文',78
union all select '李四','数学',85
union all select '李四','英语',78
go
select * from #testselect name 姓名,
sum(case km when '语文' then cj end) 语文,
sum(case km when '数学' then cj end) 数学,
sum(case km when '英语' then cj end) 英语,
sum(cj) 总分
from #test group by namedeclare @sql varchar(8000)
set @sql='select name 姓名'
select @sql= @sql+ ',sum(case km when '''+km+''' then cj end) ' + km from (select distinct km from #test) a
set @sql=@sql+',sum(cj) 总分 from #test group by name'
exec (@sql)
go
drop table #test
select *,
本日金额-上日金额 as 增长额,
( case when 本日金额-上日金额>0
then 本日金额/(本日金额-上日金额)
else 0
end
) as 增长率
from
( select 客户名称,
sum( case when 日期=replace(convert(varchar(10),getdate(),120),'-','')
then 金额
else 0
end
) as 本日金额,
sum( case when 日期=replace(convert(varchar(10),dateadd(day,-1,getdate()),120),'-','')
then 金额
else 0
end
) as 上日金额
from mytable
group by 客户名称
) a
select 客户名称=isnull(a.客户名称,b.客户名称)
,[本日金额(20030402)]=isnull(a.[本日金额(20030402)],0)
,[上日金额(20030401)]=isnull(b.[上日金额(20030401)],0)
,增长额=isnull(a.[本日金额(20030402)],0)
-isnull(b.[上日金额(20030401)],0)
,增长率=cast(cast((isnull(a.[本日金额(20030402)],0)
-isnull(b.[上日金额(20030401)],0)
)*100/isnull(b.[上日金额(20030401)],0)
as decimal(10,2))as varchar)+'%'
from(
select 客户名称,[本日金额(20030402)]=sum(金额)
from MyTable
where 日期='20030402'
group by 客户名称
)a full join(
select 客户名称,[上日金额(20030401)]=sum(金额)
from MyTable
where 日期='20030401'
group by 客户名称
)b on a.客户名称=b.客户名称
create table MyTable(日期 char(8),客户名称 varchar(10),金额 decimal(10,2))
insert MyTable select '20030401','客户1',300.00
union all select '20030401','客户1',500.00
union all select '20030401','客户2',100.00
union all select '20030402','客户1',600.00
union all select '20030402','客户1',1000.00
union all select '20030402','客户2',200.00
union all select '20030402','客户2',200.00
go--查询
select 客户名称=isnull(a.客户名称,b.客户名称)
,[本日金额(20030402)]=isnull(a.[本日金额(20030402)],0)
,[上日金额(20030401)]=isnull(b.[上日金额(20030401)],0)
,增长额=isnull(a.[本日金额(20030402)],0)
-isnull(b.[上日金额(20030401)],0)
,增长率=cast(cast((isnull(a.[本日金额(20030402)],0)
-isnull(b.[上日金额(20030401)],0)
)*100/isnull(b.[上日金额(20030401)],0)
as decimal(10,2))as varchar)+'%'
from(
select 客户名称,[本日金额(20030402)]=sum(金额)
from MyTable
where 日期='20030402'
group by 客户名称
)a full join(
select 客户名称,[上日金额(20030401)]=sum(金额)
from MyTable
where 日期='20030401'
group by 客户名称
)b on a.客户名称=b.客户名称
godrop table MyTable/*--测试结果客户名称 本日金额(20030402)上日金额(20030401) 增长额 增长率
---------- ------------------- ------------------ ---------- ---------
客户1 1600.00 800.00 800.00 100.00%
客户2 400.00 100.00 300.00 300.00%(所影响的行数为 2 行)
--*/
drop table #Table1create table #Table1(
客戶名稱 nvarchar(20),
本日金額 float,
上日金額 float,
增長額 float,
增長率 float
)insert into #Table1(客戶名稱,本日金額,上日金額)
select 客戶名稱,
sum(case datediff(dd,日期,getdate()) when 1 then 金額 else 0 end ) as 本日金額,
sum(case datediff(dd,日期,getdate()) when 1 then 金額 else 0 end ) as 本日金額
from Mytableupdate #Table1 set 增長額 = 本日金額 - 上日金額, 增長率 = (本日金額 - 上日金額) / 100select * from #Table1
left join (select 客戶名稱 ,sum(金額) 本日金額 from mytable where 日期= '2004/04/02' group by name) a on a.客戶名稱=t.客戶名稱
left join (select 客戶名稱 ,sum(金額) 前日金額 from mytable where 日期= '2004/04/01' group by name) b on b.客戶名稱=t.客戶名稱
left join (select 客戶名稱 ,sum(金額) 本日金額 from mytable where 日期= '2004/04/02' group by name) a on a.客戶名稱=t.客戶名稱
left join (select 客戶名稱 ,sum(金額) 前日金額 from mytable where 日期= '2004/04/01' group by name) b on b.客戶名稱=t.客戶名稱
再次请教:有一表名为 MyTable
有四个字段:
日期 客户名称 客户编码 金额
20030401 客户1 A0001 300.00
20030401 客户1 A0001 500.00
20030401 客户2 A0002 100.00
20030401 客户4 A0004 200.0020030402 客户1 A0001 600.00
20030402 客户1 A0001 1000.00
20030402 客户2 A0002 200.00
20030402 客户2 A0002 200.00
20030402 客户3 A0004 100.00
是否可以写一条SQL语句得到下面的结果,得出客户名称时用客户编码来Group by 客户名称 本日总金额 本日金额 比率 上日金额 增长额 增长率客户1 2100.00 1600.00 76% 800.00 800.00 100%
客户2 2100.00 400.00 19% 100.00 300.00 300%
客户3 2100.00 100.00 5% --- 100.00 --
客户4 2100.00 --- -- 100.00 -100.00 --
select 客户名称=isnull(a.客户名称,b.客户名称)
,本日总金额=isnull(a.本日总金额,0)
,本日金额=isnull(a.本日金额,0)
,比率=cast(cast(isnull(a.本日金额,0)*100
/isnull(a.本日总金额,0) as decimal(10,2)) as varchar)+'%'
,上日金额=isnull(b.上日金额,0)
,增长额=isnull(a.本日金额,0)
-isnull(b.上日金额,0)
,增长率=cast(cast((isnull(a.本日金额,0)
-isnull(b.上日金额,0)
)*100/isnull(b.上日金额,0)
as decimal(10,2))as varchar)+'%'
from(
select 客户名称,本日金额=sum(金额),本日总金额=(
select sum(金额) from MyTable
where 客户名称=a.客户名称 and 日期<=max(a.日期))
from MyTable a
where 日期='20030402'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate(),112)
group by 客户名称
)a full join(
select 客户名称,上日金额=sum(金额)
from MyTable
where 日期='20030401'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate()-1,112)
group by 客户名称
)b on a.客户名称=b.客户名称
,本日总金额=isnull(a.本日总金额,0)
,本日金额=isnull(a.本日金额,0)
,比率=case isnull(a.本日总金额,0) when 0 then '----'
else cast(cast(
isnull(a.本日金额,0)*100
/isnull(a.本日总金额,0)
as decimal(10,2)) as varchar)+'%' end
,上日金额=isnull(b.上日金额,0)
,增长额=isnull(a.本日金额,0)
-isnull(b.上日金额,0)
,增长率=case isnull(b.上日金额,0) when 0 then '----'
else cast(cast(
(isnull(a.本日金额,0)-isnull(b.上日金额,0))
*100/isnull(b.上日金额,0)
as decimal(10,2))as varchar)+'%' end
from(
select 客户名称,本日金额=sum(金额),本日总金额=(
select sum(金额) from MyTable
where 客户名称=a.客户名称 and 日期<=max(a.日期))
from MyTable a
where 日期='20030402'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate(),112)
group by 客户名称
)a full join(
select 客户名称,上日金额=sum(金额)
from MyTable
where 日期='20030401'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate()-1,112)
group by 客户名称
)b on a.客户名称=b.客户名称
--测试--测试数据
create table MyTable(日期 char(8),客户名称 varchar(10),客户编码 char(5),金额 decimal(10,2))
insert MyTable select '20030401','客户1','A0001',300.00
union all select '20030401','客户1','A0001',500.00
union all select '20030401','客户2','A0002',100.00
union all select '20030401','客户4','A0004',200.00
union all select '20030402','客户1','A0001',600.00
union all select '20030402','客户1','A0001',1000.00
union all select '20030402','客户2','A0002',200.00
union all select '20030402','客户2','A0002',200.00
union all select '20030402','客户3','A0004',100.00
go--查询
select 客户名称=isnull(a.客户名称,b.客户名称)
,本日总金额=isnull(a.本日总金额,0)
,本日金额=isnull(a.本日金额,0)
,比率=case isnull(a.本日总金额,0) when 0 then '----'
else cast(cast(
isnull(a.本日金额,0)*100
/isnull(a.本日总金额,0)
as decimal(10,2)) as varchar)+'%' end
,上日金额=isnull(b.上日金额,0)
,增长额=isnull(a.本日金额,0)
-isnull(b.上日金额,0)
,增长率=case isnull(b.上日金额,0) when 0 then '----'
else cast(cast(
(isnull(a.本日金额,0)-isnull(b.上日金额,0))
*100/isnull(b.上日金额,0)
as decimal(10,2))as varchar)+'%' end
from(
select 客户名称,本日金额=sum(金额),本日总金额=(
select sum(金额) from MyTable
where 客户名称=a.客户名称 and 日期<=max(a.日期))
from MyTable a
where 日期='20030402'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate(),112)
group by 客户名称
)a full join(
select 客户名称,上日金额=sum(金额)
from MyTable
where 日期='20030401'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate()-1,112)
group by 客户名称
)b on a.客户名称=b.客户名称
godrop table MyTable/*--测试结果客户名称 本日总金额 本日金额 比率 上日金额 增长额 增长率
-------- ------------ ----------- ---------- --------- ---------- ---------
客户1 2400.00 1600.00 66.67% 800.00 800.00 100.00%
客户2 500.00 400.00 80.00% 100.00 300.00 300.00%
客户3 100.00 100.00 100.00% .00 100.00 ----
客户4 .00 .00 ---- 200.00 -200.00 -100.00%(所影响的行数为 4 行)
--*/
增长率=cast(cast((isnull(a.本日金额,0)
-isnull(b.上日金额,0)
)*100/isnull(b.上日金额,0)
as decimal(10,2))as varchar)+'%' from
(select 客户名称,sum(金额) '本日金额'
from mytable
where datepart(dd,convert(datetime,日期))=datepart(dd,getdate())
group by 客户名称)a,(select 客户名称,sum(金额)'上日金额'
from mytable
where convert(int,substring(日期,7,2))=datepart(dd,getdate())-1
group by 客户名称)b
where a.客户名称=b.客户名称
Insert into @Test Select '20030401','客户1',300.00
Union All Select '20030401','客户1',500.00
Union All Select '20030401','客户2',100.00
Union All Select '20030402','客户1',600.00
Union All Select '20030402','客户1',1000.00
Union All Select '20030402','客户2',200.00
Union All Select '20030402','客户2',200.00Select 客户名称 ,本日金额=(Select Sum(金额) From @Test B Where B.客户名称=A.客户名称 AND 日期='20030402'),
上日金额=(Select Sum(金额) From @Test B Where B.客户名称=A.客户名称 AND 日期=DATEADD(day,-1,'20030402')),
增长额=(Select Sum(金额) From @Test B Where B.客户名称=A.客户名称 AND 日期='20030402')-(Select Sum(金额) From @Test B Where B.客户名称=A.客户名称 AND 日期=DATEADD(day,-1,'20030402'))
From @Test A Group By 客户名称
create table MyTable(日期 char(8),客户名称 varchar(10),客户编码 char(5),金额 decimal(10,2))
insert MyTable select '20030401','客户1','A0001',300.00
union all select '20030401','客户1','A0001',500.00
union all select '20030401','客户2','A0002',100.00
union all select '20030401','客户4','A0004',200.00
union all select '20030402','客户1','A0001',600.00
union all select '20030402','客户1','A0001',1000.00
union all select '20030402','客户2','A0002',200.00
union all select '20030402','客户2','A0002',200.00
union all select '20030402','客户3','A0004',100.00
go--查询
select 客户名称=isnull(a.客户名称,b.客户名称)
,本日总金额=isnull(a.本日总金额,0)
,本日金额=isnull(a.本日金额,0)
,比率=case isnull(a.本日总金额,0) when 0 then '----'
else cast(cast(
isnull(a.本日金额,0)*100
/isnull(a.本日总金额,0)
as decimal(10,2)) as varchar)+'%' end
,上日金额=isnull(b.上日金额,0)
,增长额=isnull(a.本日金额,0)
-isnull(b.上日金额,0)
,增长率=case isnull(b.上日金额,0) when 0 then '----'
else cast(cast(
(isnull(a.本日金额,0)-isnull(b.上日金额,0))
*100/isnull(b.上日金额,0)
as decimal(10,2))as varchar)+'%' end
from(
select 客户名称,本日金额=sum(金额),本日总金额=(
select sum(金额) from MyTable
where 客户名称=a.客户名称 and 日期<=max(a.日期))
from MyTable a
where 日期='20030402'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate(),112)
group by 客户名称
)a full join(
select 客户名称,上日金额=sum(金额)
from MyTable
where 日期='20030401'
--如果是取系统时间,则条件改为:where 日期=convert(char(8),getdate()-1,112)
group by 客户名称
)b on a.客户名称=b.客户名称
godrop table MyTable/*--测试结果客户名称 本日总金额 本日金额 比率 上日金额 增长额 增长率
-------- ------------ ----------- ---------- --------- ---------- ---------
客户1 2400.00 1600.00 66.67% 800.00 800.00 100.00%
客户2 500.00 400.00 80.00% 100.00 300.00 300.00%
客户3 100.00 100.00 100.00% .00 100.00 ----
客户4 .00 .00 ---- 200.00 -200.00 -100.00%(所影响的行数为 4 行)