请教这个语句怎么实现

[hqbj08]

有两个标如下：

MID M_c Syear
1 ABC 2008
2 AAC 2008

ID MID M_Module M_Value
1 1 A 15
2 1 B 2
3 1 C 3
4 1 D 1
5 2 A 24
6 2 B 1
7 2 C 32
8 2 D 41

导出如下：
MID M_c Syear A B C D
1 ABC 2008 15 2 3 1
2 AAC 2008 24 1 32 41

请帮忙看一下，谢谢！

[huzhaoloving]

不好意思。。看不懂

[飘零一叶]

IF(OBJECT_ID('Student') IS NOT NULL)
  DROP TABLE Student
   CREATE TABLE Student
   (
       ID INT IDENTITY(1,1) PRIMARY KEY,
       Name VARCHAR(10)
   )
  INSERT  Student
  SELECT '张三' UNION  
  SELECT '李四' UNION  
  SELECT '王五'
IF(OBJECT_ID('Grade') IS NOT null)
 DROP TABLE Grade
CREATE TABLE Grade
(
  SID INT ,     --对应着 student 中的ID
  SUBJECT VARCHAR(50),
  VALUE INT,
  order_index INT , --在行上的顺寻
  flag    int           --是否显示
)
INSERT grade VALUES(1,'English',66,1,1)
INSERT grade VALUES(1,'Chinese',67,2,1)
INSERT grade VALUES(1,'Math',68,3,1)
INSERT grade VALUES(2,'English',63,1,1)
INSERT grade VALUES(2,'Chinese',64,2,1)
INSERT grade VALUES(2,'Math',65,3,1) 
INSERT grade VALUES(2,'Franch',65,4,1) 
SELECT * FROM Student s
SELECT * FROM Grade

DECLARE @S NVARCHAR(MAX)
SET @S='SELECT S.ID,S.NAME'
SELECT @S=@S+',MAX(CASE WHEN SUBJECT='''+SUBJECT+''' THEN VALUE ELSE 0 END)'+SUBJECT FROM Grade WHERE FLAG='1' GROUP BY SUBJECT,order_index ORDER BY order_index
SET @S=@S+' FROM STUDENT S LEFT JOIN GRADE G ON S.ID=G.SID GROUP BY S.ID,S.NAME'
EXEC (@S)

--使用PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合 
DECLARE @P NVARCHAR(MAX)
set @P=''
SELECT @P=@P+',['+ SUBJECT+']' FROM Grade WHERE flag='1' GROUP BY SUBJECT,order_index ORDER BY order_index
SET @P=SUBSTRING(@P,2,LEN(@P))
EXEC ('SELECT ID,NAME,'+@P+' from (Select s.id,s.name,g.SUBJECT,g.value from Student s left join Grade g on s.id=g.sid) x 
pivot(sum(value) for subject in('+@P+')) c')

参考一下

[GUOCHENGJUN]

create table tb(id int,MID int , M_Module varchar(10) , M_Value int)
insert into tb values(1 ,1,'A' , 15)
insert into tb values(2 ,1,'B' , 2)
insert into tb values(3 ,1,'C' , 3)
insert into tb values(4 ,1,'D' , 1)
insert into tb values(5 ,2,'A' , 24)
insert into tb values(6 ,2,'B' , 1)
insert into tb values(7 ,2,'C' , 32)
insert into tb values(8 ,2,'D' , 41)

select MID ,
max(case M_Module when 'A' then M_Value else 0 end) A,
max(case M_Module when 'B' then M_Value else 0 end) B,
max(case M_Module when 'C' then M_Value else 0 end) C,
max(case M_Module when 'D' then M_Value else 0 end) CD
from tb
group by MID

[浑沌滴年代]

行转列，用透视

[hao1hao2hao3]

列固定（ABCD四列）就用下面的
select a.mid,a.m_c,
sum(case when b.m_module ='A' then m_value else 0 end) as A,
sum(case when b.m_module ='B' then m_value else 0 end) as B,
sum(case when b.m_module ='C' then m_value else 0 end) as C,
sum(case when b.m_module ='D' then m_value else 0 end) as D
from a join b
on a.mid = b.mid 
group by a.mid ,a.m_c

不固定就用动态SQL

[GUOCHENGJUN]

select a.MID ,b.syear
  max(case a.M_Module when 'A' then a.M_Value else 0 end) A,
  max(case a.M_Module when 'B' then a.M_Value else 0 end) B,
  max(case a.M_Module when 'C' then a.M_Value else 0 end) C,
  max(case a.M_Module when 'D' then a.M_Value else 0 end) D
from tb a INNER JOION tb1 b
on a.MID=b.Mid
group by a.MID,b.syear

[GUOCHENGJUN]

select a.MID ,b.m_c,b.syear
  max(case a.M_Module when 'A' then a.M_Value else 0 end) A,
  max(case a.M_Module when 'B' then a.M_Value else 0 end) B,
  max(case a.M_Module when 'C' then a.M_Value else 0 end) C,
  max(case a.M_Module when 'D' then a.M_Value else 0 end) D
from tb a INNER JOION tb1 b
on a.MID=b.Mid
group by a.MID ,b.m_c,b.syear

[Mark杨]

declare @ta table(MID int,M_c varchar(10),Syear varchar(10))
insert @ta 
select 1,'ABC','2008' union all
select 2,'AAC','2008'

declare @tb table(ID int,MID int,M_Module varchar(10),M_Value varchar(10))
insert @tb
select 1,1,'A','15' union all
select 2,1,'B','2' union all
select 3,1,'C','3' union all
select 4,1,'D','1' union all
select 5,2,'A','24' union all
select 6,2,'B','1' union all
select 7,2,'C','32' union all
select 8,2,'D','41' 

select a.mid,a.m_c,
A = sum(case when b.m_module ='A' then m_value else 0 end),
B = sum(case when b.m_module ='B' then m_value else 0 end),
C = sum(case when b.m_module ='C' then m_value else 0 end),
D = sum(case when b.m_module ='D' then m_value else 0 end)
from @ta a join @tb b
on a.mid = b.mid 
group by a.mid ,a.m_c order by a.mid

/*
mid         m_c        A           B           C           D           
----------- ---------- ----------- ----------- ----------- ----------- 
1           ABC        15          2           3           1
2           AAC        24          1           32          41

（所影响的行数为 2 行）
*/

[jamk]

楼上正解，不过少了行Syear 呵呵

[hqbj08]

谢谢大家，给分