A表数据有误
只有一条1001,张三如果B表的数据是这样,你想要的结果是什么样的
1001,1,64,78,80,2014-10-10
1001,1,74,74,85,2014-10-10
1001,1,64,78,80,2014-10-11
1001,1,74,74,85,2014-10-11最终效果:
Id,Name,(2014-10-10)Chinese,(2014-10-10)Math,(2014-10-10)EngList
1001,张三, 138,152,165
只有一条1001,张三如果B表的数据是这样,你想要的结果是什么样的
1001,1,64,78,80,2014-10-10
1001,1,74,74,85,2014-10-10
1001,1,64,78,80,2014-10-11
1001,1,74,74,85,2014-10-11最终效果:
Id,Name,(2014-10-10)Chinese,(2014-10-10)Math,(2014-10-10)EngList
1001,张三, 138,152,165
gowith A(id,name) as(
select '1001','threebrother'
union all
select '1001','threebrother'
),
B(id,chinese,math,english,date) as
(
select '1001',64,78,80,'2014-10-10'
union all
select '1001',74,74,85,'2014-10-10'
)
select distinct A.id,A.name,chinese,math,english
from(
select id,date
,sum(chinese) as 'chinese'
,sum(math) as 'math'
,sum(english) as 'english'
from B
group by id,date
) as t
Join A on A.id=t.id
,SUM(T2.math)math,SUM(T2.english)english
FROM A T1
LEFT JOIN B T2 ON T1.id=T2.id
GROUP BY T1.id,T1.name,T2.date其实我就想知道,你说的行转列是用在多个日期上吗?
如果直接表关联聚合,你也不会来论坛问了因为你想要的效果,列中有包含日期信息,因此我想知道如果有多个日期,你需要怎么排列
create table A表(Id int,Name varchar(10))create table B表
(Id int,Sex bit,Chinese tinyint,Math tinyint,English tinyint,StatisticalDate varchar(20))insert into A表(Id,Name)
select 1001,'张三'insert into B表(Id,Sex,Chinese,Math,English,StatisticalDate)
select 1001,1,64,78,80,'2014-10-10' union all
select 1001,1,74,74,85,'2014-10-10'
declare @tsql varchar(6000)select @tsql=isnull(@tsql+',','')
+'sum(case when StatisticalDate='''+StatisticalDate+''' then Chinese else 0 end) ''('+StatisticalDate+')Chinese'','
+'sum(case when StatisticalDate='''+StatisticalDate+''' then Math else 0 end) ''('+StatisticalDate+')Math'','
+'sum(case when StatisticalDate='''+StatisticalDate+''' then English else 0 end) ''('+StatisticalDate+')English'''
from (select distinct StatisticalDate
from B表) t
select @tsql='select b.Id,a.Name,'+@tsql
+' from B表 b '
+' inner join A表 a on b.Id=a.Id '
+' group by b.Id,a.Name 'exec(@tsql)/*
Id Name (2014-10-10)Chinese (2014-10-10)Math (2014-10-10)English
----------- ---------- ------------------- ---------------- -------------------
1001 张三 138 152 165(1 row(s) affected)
*/