sql 难题
A表
年份,职工代码,姓名,得分,及格分,课程,是否合格,人品
2010,101, 张三,30, 60, 文化课,否,好
2010,102,李四,40,60,文化课,否,好
2010,103,王五,70,60,文化课,是,好
2010,101,张三,70,60,思想课,是,好
2010,105,刘飞,50,60,思想课,否,好
求结果:
年份,职工代码,姓名,人品,文化课得分,文化课是否及格,思想课得分,思想课是否及格
2010,101 ,张三 ,好, 30 ,否, 70, 是
2010,102, 李四, 好, 40, 否, NULL, NULL
2010,103 , 王五, 好, 70, 是, NULL, NULL
2010,105, 刘飞, 好, NULL ,NULL ,50 , 否
要是只是一列课程动态变化好弄.行转列
但是现在是课程和是否及格2个字段.求大牛指点,最好给出代码,多谢!
[最优解释]
--if object_id('A') is not null
-- drop table A
--Go
--Create table A([年份] nvarchar(4),[职工代码] smallint,[姓名] nvarchar(2),[得分] smallint,[及格分] smallint,[课程] nvarchar(3),[是否合格] nvarchar(1),[人品] nvarchar(1))
--Insert into A
--Select '2010',101,N'张三',30,60,N'文化课',N'否',N'好'
--Union all Select '2010',101,N'张三',70,60,N'思想课',N'是',N'好'
--Union all Select '2010',102,N'李四',40,60,N'文化课',N'否',N'好'
--Union all Select '2010',103,N'王五',70,60,N'文化课',N'是',N'好'
--Union all Select '2010',105,N'刘飞',50,60,N'思想课',N'否',N'好'
SELECT * FROM a
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(课程+'得分')+'=max(case when [课程]='+quotename(课程,'''')+' then [得分] else null end)'
+','+quotename(课程+'是否及格')+'=max(case when [课程]='+quotename(课程,'''')+' then [是否合格] else null end)'
from A group by 课程
exec('select 年份,职工代码,姓名,人品,及格分'+@s+' from A group by 年份,职工代码,姓名,人品,及格分')
/*
年份 职工代码 姓名 人品 及格分 思想课得分 思想课是否及格 文化课得分 文化课是否及格
---- ------ ---- ---- ------ ------ ------- ------ -------
2010 101 张三 好 60 70 是 30 否
2010 102 李四 好 60 NULL NULL 40 否
2010 103 王五 好 60 NULL NULL 70 是
2010 105 刘飞 好 60 50 否 NULL NULL
警告: 聚合或其他 SET 操作消除了 Null 值。
*/
use tempdb
go
-->生成表A
if object_id('A') is not null
drop table A
Go
Create table A([年份] nvarchar(4),[职工代码] smallint,[姓名] nvarchar(2),[得分] smallint,[及格分] smallint,[课程] nvarchar(3),[是否合格] nvarchar(1),[人品] nvarchar(1))
Insert into A
Select '2010',101,N'张三',30,60,N'文化课',N'否',N'好'
Union all Select '2010',102,N'李四',40,60,N'文化课',N'否',N'好'
Union all Select '2010',103,N'王五',70,60,N'文化课',N'是',N'好'
Union all Select '2010',101,N'张三',70,60,N'思想课',N'是',N'好'
Union all Select '2010',105,N'刘飞',50,60,N'思想课',N'否',N'好'
declare @sql nvarchar(2000)
set @sql='SELECT 年份
,职工代码
,姓名
,人品'
select @sql=@sql+',max(case when [课程]='''+[课程]+''' then 得分 end) as ['+[课程]+'得分]'
+',max(case when [课程]='''+[课程]+''' and 得分>=及格分 then ''是'' when [课程]='''+[课程]+''' then ''否'' end) as ['+[课程]+'得分是否及格]'
FROM A
GROUP BY [课程]
order by [课程] desc
exec(@sql+' from A group by 年份,职工代码,姓名,人品')
/*
年份职工代码姓名人品文化课得分文化课得分是否及格思想课得分思想课得分是否及格
2010101张三好30否70是
2010102李四好40否NULLNULL
2010103王五好70是NULLNULL
2010105刘飞好NULLNULL50否
*/
USE test
GO
-->生成表A
if object_id('A') is not null
drop table A
Go
Create table A([年份] nvarchar(4),[职工代码] smallint,[姓名] nvarchar(2),[得分] smallint,[及格分] smallint,[课程] nvarchar(3),[是否合格] nvarchar(1),[人品] nvarchar(1))
Insert into A
Select '2010',101,N'张三',30,60,N'文化课',N'否',N'好'
Union all Select '2010',102,N'李四',40,60,N'文化课',N'否',N'好'
Union all Select '2010',103,N'王五',70,60,N'文化课',N'是',N'好'
Union all Select '2010',101,N'张三',70,60,N'思想课',N'是',N'好'
Union all Select '2010',105,N'刘飞',50,60,N'思想课',N'否',N'好'
SELECT
年份
,职工代码
,姓名
,人品
,MAX(CASE WHEN 课程=N'文化课' THEN 得分 END) AS 文化课得分
,MAX(CASE WHEN 课程=N'文化课' THEN CASE WHEN 得分>及格分 THEN '是' ELSE '否' END END) AS 文化课得分
,MAX(CASE WHEN 课程=N'思想课' THEN 得分 END) AS 思想课得分
,MAX(CASE WHEN 课程=N'思想课' THEN CASE WHEN 得分>及格分 THEN '是' ELSE '否' END END) AS 思想课得分
FROM A
GROUP BY 年份,职工代码,姓名,人品
/*
年份 职工代码 姓名 人品 文化课得分 文化课得分 思想课得分 思想课得分
---- -------- ----- ----- ----------- ---------- ----------- ----------
2010 101 张三 好 30 否 70 是
2010 102 李四 好 40 否 NULL NULL
2010 103 王五 好 70 是 NULL NULL
2010 105 刘飞 好 NULL NULL 50 否
*/