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 值。
*/
[其他解释]
--引用2楼小ben的例子
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 否
*/
[其他解释]
在线等啊.结果对立马结贴,大家帮忙啊.
[其他解释]
...结果是的.但是课程是写死的.要是写成动态的就好了.
我线自己改试试,要是能改好分就都给你了.最好有那位大牛帮忙改下啊!
[其他解释]
没改好啊.大家帮帮忙把!
[其他解释]
要写成动态
[其他解释]
我的不是动态吗？
[其他解释]
谢谢大家了.答案已经出来了.我多试试,看看还有什么bug吗.试玩就结贴啦.