参考: txlicenhe(马可)
1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)想变成姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78
declare @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) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
1: 列转为行:
eg1:
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)想变成姓名 语文 数学 英语
张三 80 86 75
李四 78 85 78
declare @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) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
select id
,name
,year(min(t_date))
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
and month(t_date) = 1
) as '1'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
and month(t_date) = 12
) as '12'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
) as '12个月合计'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
) * 0.17 as 税
from T a
group by id,name,datediff(year,t_date,getdate())
select id
,name
,year(min(t_date))
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
and month(t_date) = 1
) as '1'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
and month(t_date) = 12
) as '12'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
) as '12个月合计'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,a.t_date) = 0
) * 0.17 as 税
from T a
where year(t_date) = 2003
group by id,name,datediff(year,t_date,getdate())
id name get_pay t_date
001 张三 1200 1999-10-15
001 张三 2000 2003-1-15
select tba.id,tba.name,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 1 and id = tba.id) as 1月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 2 and id = tba.id) as 2月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 3 and id = tba.id) as 3月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 4 and id = tba.id) as 4月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 5 and id = tba.id) as 5月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 6 and id = tba.id) as 6月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 7 and id = tba.id) as 7月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 8 and id = tba.id) as 8月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 9 and id = tba.id) as 9月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 10 and id = tba.id) as 10月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 11 and id = tba.id) as 11月,
(select get_pay from tablename where year(t_date) = 2003 and month(t_date) = 12 and id = tba.id) as 12月,
(select sum(get_pay) from tablename where year(t_date) = 2003 and id = tba.id) as 合計,
(select sum(get_pay) from tablename where year(t_date) = 2003 and id = tba.id) * 0.17 as 稅
from
(select distinct id,name from tablename where year(t_date) = 2003 ) as tba
“提示a.t_date在选择表中无效,该列既未包含在聚合函数中,也不包含在group by子句中”这个问题怎么解决呀?请多指教。
to fanmb(我要努力) :
我想问一下,你的语句用这种动态SQL如何改写呀?declare @sql varchar(8000)
set @sql = 'select id
select @sql = @sql + ',sum(case t_date when '''+t_date+''' then get_pay end) ['+t_date+']'
from (select distinct t_date from gzjsb) as a
select @sql = @sql+' from gzjsb group by id
exec(@sql)总提示字符串向datetime转换时出错??
,min(name)
,year(min(t_date))
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,min(a.t_date)) = 0
and month(t_date) = 1
) as '1'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,min(a.t_date)) = 0
and month(t_date) = 12
) as '12'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,min(a.t_date)) = 0
) as '12个月合计'
,(select sum(get_pay)
from T
where id = a.id
and datediff(year,t_date,min(a.t_date)) = 0
) * 0.17 as 税
from T a
--where year(t_date) = 2003
group by id,datediff(year,t_date,getdate())
order by datediff(year,t_date,getdate())
下面的两问题是什么意思呀?datediff(year,t_date,getdate())也参与分组是做什么用的,order by datediff(year,t_date,getdate())的排序有用吗?
我的查询是针对所有年度的!datediff(year,t_date,getdate())
按年分组按年排序