假设有个科目类别表(Subject)如下:
SubjectID Subject
1 语文
2 数学
3 物理
假设有张学生成绩表(tb)如下:
Name SubjectID Result
张三 1 74
张三 2 83
张三 3 93
李四 1 74
李四 2 84
李四 3 94
科目类别表是满足约束的主从表关系,也就是SubjectID在成绩表(tb)
中作为外键。
现在我想得到如下的结果集:
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
但是表头语文,数学,物理只能得到他的ID:
/*
姓名 1 2 3
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
各位知道关联表的是怎么写行列转换的吗?
最好有sql语句,小弟感激不尽!
SubjectID Subject
1 语文
2 数学
3 物理
假设有张学生成绩表(tb)如下:
Name SubjectID Result
张三 1 74
张三 2 83
张三 3 93
李四 1 74
李四 2 84
李四 3 94
科目类别表是满足约束的主从表关系,也就是SubjectID在成绩表(tb)
中作为外键。
现在我想得到如下的结果集:
/*
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
但是表头语文,数学,物理只能得到他的ID:
/*
姓名 1 2 3
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
*/
各位知道关联表的是怎么写行列转换的吗?
最好有sql语句,小弟感激不尽!
姓名=name,
语文=MAX(case when [subjec]='语文' then result else 0 end),
数学=MAX(case when [subjec]='数学' then result else 0 end),
英语=MAX(case when [subjec]='英语' then result else 0 end)
from Subject s join tb on s.SubjectID=tb.SubjectID
group by name
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( SubjectID int, [Subject] varchar(10))
go
insert tb SELECT 1, '语文' UNION ALL SELECT
2, '数学' UNION ALL SELECT
3 , '物理'
go
IF OBJECT_ID('Subject') IS NOT NULL
DROP TABLE Subject
GO
CREATE TABLE Subject(Name varchar(10), SubjectID int, Result int )
go
insert [Subject] SELECT
'张三', 1,74 UNION ALL SELECT
'张三', 2,83 UNION ALL SELECT
'张三', 3,93 UNION ALL SELECT
'李四', 1,74 UNION ALL SELECT
'李四' , 2,84 UNION ALL SELECT
'李四' ,3,94
go
select
姓名=name,
语文=MAX(case when [Subject]='语文' then result else 0 end),
数学=MAX(case when [Subject]='数学' then result else 0 end),
英语=MAX(case when[Subject]='物理' then result else 0 end)
from Subject s join tb on s.SubjectID=tb.SubjectID
group by name
姓名 语文 数学 英语
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
NAME AS 姓名,
MAX(CASE WHEN SubjectID =1 THEN Result ELSE 0 END )AS 语文,
MAX(CASE WHEN SubjectID =2 THEN Result ELSE 0 END )AS 数学,
MAX(CASE WHEN SubjectID =3 THEN Result ELSE 0 END )AS 英语
FROM SUBJECTS S JOIN TB T ON S.SubjectID=T.SubjectIDGROUP BY S.NAME
DROP TABLE tb
GO
CREATE TABLE tb( SubjectID int, [Subject] varchar(10))
go
insert tb SELECT 1, '语文' UNION ALL SELECT
2, '数学' UNION ALL SELECT
3 , '物理'
go
IF OBJECT_ID('Subject') IS NOT NULL
DROP TABLE Subject
GO
CREATE TABLE Subject(Name varchar(10), SubjectID int, Result int )
go
insert [Subject] SELECT
'张三', 1,74 UNION ALL SELECT
'张三', 2,83 UNION ALL SELECT
'张三', 3,93 UNION ALL SELECT
'李四', 1,74 UNION ALL SELECT
'李四' , 2,84 UNION ALL SELECT
'李四' ,3,94
go
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Subject])+'=max(case when [Subject]='+quotename([Subject],'''')+' then [Result] else 0 end)'
from tb T --PRINT @S
exec('select [Name]'+@s+' from TB T JOIN SUBJECT S ON T.SubjectID=S.SubjectID group by [Name]')
(所影响的行数为 3 行)
(所影响的行数为 6 行)Name 语文 数学 物理
---------- ----------- ----------- -----------
李四 74 84 94
张三 74 83 93
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( SubjectID int, [Subject] varchar(10))
go
insert tb SELECT 1, '语文' UNION ALL SELECT
2, '数学' UNION ALL SELECT
3 , '物理'
go
IF OBJECT_ID('Subject') IS NOT NULL
DROP TABLE Subject
GO
CREATE TABLE Subject(Name varchar(10), SubjectID int, Result int )
go
insert [Subject] SELECT
'张三', 1,74 UNION ALL SELECT
'张三', 2,83 UNION ALL SELECT
'张三', 3,93 UNION ALL SELECT
'李四', 1,74 UNION ALL SELECT
'李四' , 2,84 UNION ALL SELECT
'李四' ,3,94
go
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+Subject+']=max( case when [subject]='''+Subject+''' then result else 0 end)'
from tb group by Subjectexec ('select 姓名= name '+','+@s +'from Subject s join tb on s.SubjectID=tb.SubjectID group by name')姓名 数学 物理 语文
---------- ----------- ----------- -----------
李四 84 94 74
张三 83 93 74