create table a
(
学号 int null,
姓名 nvarchar(20) null
)
insert into a (学号,姓名)
select 1001 , '王一' union all
select 1000 ,'李成'
go
create table b
(
学号 int null,
科目 nvarchar(20) null,
成绩 int null
)
insert into b(学号,科目,成绩)
select 1001 ,'英语', 89 union all
select 1001 ,'数学', 87 union all
select 1000 ,'语文', 76 union all
select 1000 ,'数学', 98 union all
select 1000, '英语', 86
--1
SELECT * FROM (SELECT a.学号,a.姓名,b.科目,b.成绩 FROM a a INNER JOIN b b ON a.学号=b.学号) q
PIVOT( max(成绩) FOR 科目 IN(语文,数学,英语)) w
go
--2
WITH cte1 AS (
SELECT a.姓名,b.科目,b.成绩
FROM a a INNER JOIN b b ON a.学号=b.学号
)
SELECT cte1.姓名,
max(CASE cte1.科目 WHEN '语文' THEN cte1.成绩 ELSE 0 END) AS 语文,
max(CASE cte1.科目 WHEN '数学' THEN cte1.成绩 ELSE 0 END) AS 数学,
max(CASE cte1.科目 WHEN '英语' THEN cte1.成绩 ELSE 0 END) AS 英语
FROM cte1
GROUP BY cte1.姓名
go
--3
WITH cte1 AS (
SELECT a.姓名,b.科目,b.成绩
FROM a a INNER JOIN b b ON a.学号=b.学号
)
SELECT DISTINCT T.姓名,
(SELECT max(成绩) FROM cte1 WHERE 姓名=t.姓名 AND 科目 = '语文') AS '语文',
(SELECT max(成绩) FROM cte1 WHERE 姓名=t.姓名 AND 科目 = '数学') AS '数学',
(SELECT max(成绩) FROM cte1 WHERE 姓名=t.姓名 AND 科目 = '英语') AS '英语'
FROM cte1 T
4
DECLARE @str NVARCHAR(max)
SELECT @str=Isnull(@str+'],[','')+科目 FROM b GROUP BY 科目
SET @str ='[' + @str + ']'
PRINT @str
SET @str= 'select * from (SELECT aa.学号,aa.姓名,bb.科目,bb.成绩 FROM a aa INNER JOIN b bb ON aa.学号=bb.学号) q pivot( max(成绩) for 科目 in (' + @str + ') ) w '
EXEC (@str)
