公司的一个项目,这个SQL搞了一两天没整出来..领导要发飙了现有表 "银行分期" 结构如下:名称 还款日期 还款金额
-------- ---------- --------
A公司 2012-3-1 5000
A公司 2012-6-1 3000
B公司 2012-3-1 5000
B公司 2012-4-1 5000
B公司 2012-5-1 8000
.....
(每个公司都有多条还款记录,上限是20条.)现在,请问用SQL如何生成发下的格式(把一个客户的多条还款记录,生成一条),
名称 还款日期1 还款金额1 还款日期2 还款金额2 还款日期3 还款金额3 ...
-------- ---------- -------- --------- ---------- ----------- ----------
A公司 2012-3-1 5000 2012-6-1 3000 2012-3-1 5000
B公司 2012-4-1 5000 2012-5-1 8000
.....
-------- ---------- --------
A公司 2012-3-1 5000
A公司 2012-6-1 3000
B公司 2012-3-1 5000
B公司 2012-4-1 5000
B公司 2012-5-1 8000
.....
(每个公司都有多条还款记录,上限是20条.)现在,请问用SQL如何生成发下的格式(把一个客户的多条还款记录,生成一条),
名称 还款日期1 还款金额1 还款日期2 还款金额2 还款日期3 还款金额3 ...
-------- ---------- -------- --------- ---------- ----------- ----------
A公司 2012-3-1 5000 2012-6-1 3000 2012-3-1 5000
B公司 2012-4-1 5000 2012-5-1 8000
.....
--行列互转
--摘自中国风博客,引用请标明内容来源
--1、行换列
if object_id('Class') is not null
drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='
+quotename([Course],'''')+' then [Score] else 0 end)'
from
Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
--动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/go
--加上总成绩(学科平均分)--2000方法:
--动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
--isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
*/
create table 银行分期
(名称 varchar(6), 还款日期 date, 还款金额 int)insert into 银行分期
select 'A公司', '2012-3-1', 5000 union all
select 'A公司', '2012-6-1', 3000 union all
select 'B公司', '2012-3-1', 5000 union all
select 'B公司', '2012-4-1', 5000 union all
select 'B公司', '2012-5-1', 8000
;with t as
(select 名称,
'还款日期'+cast(row_number() over(partition by 名称 order by getdate()) as varchar(5)) 还款日期n,还款日期,
'还款金额'+cast(row_number() over(partition by 名称 order by getdate()) as varchar(5)) 还款金额n,还款金额
from 银行分期
)
select 名称,
max([还款日期1]) [还款日期1],max([还款金额1]) [还款金额1],
max([还款日期2]) [还款日期2],max([还款金额2]) [还款金额2],
max([还款日期3]) [还款日期3],max([还款金额3]) [还款金额3]
from t
pivot(max(还款日期) for 还款日期n in([还款日期1],[还款日期2],[还款日期3])) p1
pivot(max(还款金额) for 还款金额n in([还款金额1],[还款金额2],[还款金额3])) p2
group by 名称
名称 还款日期1 还款金额1 还款日期2 还款金额2 还款日期3 还款金额3
------ ---------- ----------- ---------- ----------- ---------- -----------
A公司 2012-03-01 5000 2012-06-01 3000 NULL NULL
B公司 2012-03-01 5000 2012-04-01 5000 2012-05-01 8000(2 row(s) affected)
(
name varchar(10),
riqi varchar(10),
jine int
)
insert into t1
select 'A公司', '2012-3-1', 5000 union all
select 'A公司', '2012-6-1', 3000 union all
select 'B公司', '2012-3-1', 5000 union all
select 'B公司', '2012-4-1', 5000 union all
select 'B公司', '2012-5-1', 8000
select * from t1declare @sql varchar(8000)
set @sql='select name'
select @sql=@sql+',max(case when riqi='''+riqi+''' then jine else 0 end) as ['+riqi+']'
from (select distinct riqi from t1) as aaa
set @sql=@sql+' from t1 group by name'
print @sql
exec (@sql)----------------------
name 2012-3-1 2012-4-1 2012-5-1 2012-6-1
A公司 5000 0 0 3000
B公司 5000 5000 8000 0