行列转换问题？

Question

我有一张表，保存有多个年度的两项考核结果
如
name 　nd 　khlb 　khjg
aaaa 　2009 　a  　优秀
aaaa   2009   b    称职
aaaa   2008   a    良好
aaaa   2008   b    称职因为类别是固定的，按年度很好转换
执行以下语句
SELECT     name, nd AS 年度, [a] AS aa, [b] AS bb
FROM         khqkb PIVOT (sum(khjg) FOR lb IN ([a], [b])) AS pvt
即可得出
name  nd    aa    bb
aaaa  2009  优秀　职称
aaaa  2008  良好　职称我希望按类别查询出如下的结果
name  khlb  2009  2008　2007　2006
aaaa  a     优秀　良好　
aaaa  b     称职　称职　这该如何写查询语句呢

Answer 1

参考：
create table cjb(姓名 char (10),课程名 char (10),成绩 [decimal](12, 2))
insert into cjb select '张',    '数据库'  ,  78 union
select '张' ,   '信息管理' , 80 union
select '张',    '专业英语' , 89 union
select '李',    '数据库'  ,  90 union
select '李' ,   '信息管理' , 67 union
select '李' ,   '专业英语', 56declare @sql varchar(8000)
set @sql=''select @sql=@sql + ',['+rtrim(课程名)+']=max(case 课程名 when '''+rtrim(课程名)+''' then rtrim(成绩) end)'
from cjb group by 课程名exec('select 姓名'+@sql+'from  cjb group by 姓名' )
go

Answer 2

--> 生成测试数据表: [khqkb]
IF OBJECT_ID('[khqkb]') IS NOT NULL
DROP TABLE khqkb
GO
CREATE TABLE khqkb ([name] [nvarchar](10),[nd] [int],[khlb] [nvarchar](10),[khjg] [nvarchar](10))
INSERT INTO khqkb
SELECT 'aaaa','2009','a','优秀' UNION ALL
SELECT 'aaaa','2009','b','称职' UNION ALL
SELECT 'aaaa','2008','a','良好' UNION ALL
SELECT 'aaaa','2008','b','称职'--SELECT * FROM [khqkb]-->SQL查询如下:
SELECT * 
FROM khqkb 
PIVOT(MAX(khjg) 
FOR nd IN ([2009], [2008], [2007], [2006])) AS pvt
/*
name       khlb       2009       2008       2007       2006
---------- ---------- ---------- ---------- ---------- ----------
aaaa       a          优秀         良好         NULL       NULL
aaaa       b          称职         称职         NULL       NULL(2 行受影响)
*/

Answer 3

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TBCREATE TABLE TB(NAME CHAR(5),ND CHAR(5),KHLB CHAR(2),KHJG CHAR(5))INSERT INTO TB
SELECT 'aaaa','2009','a','优秀' UNION ALL
SELECT 'aaaa','2009','b','称职' UNION ALL
SELECT 'aaaa','2008','a','良好' UNION ALL
SELECT 'aaaa','2008','b','称职'SELECT
NAME
,KHLB
,MAX(CASE ND WHEN 2009 THEN KHJG END) AS [2009]
,MAX(CASE ND WHEN 2008 THEN KHJG END) AS [2008] 
,MAX(CASE ND WHEN 2007 THEN KHJG END) AS [2007]
,MAX(CASE ND WHEN 2006 THEN KHJG END) AS [2006]
FROM TB
GROUP BY NAME,KHLBDROP TABLE TB

Answer 4

--> 生成测试数据表: [khqkb]
IF OBJECT_ID('[khqkb]') IS NOT NULL
    DROP TABLE khqkb
GO
CREATE TABLE khqkb ([name] [nvarchar](10),[nd] [int],[khlb] [nvarchar](10),[khjg] [nvarchar](10))
INSERT INTO khqkb
SELECT 'aaaa','2009','a','优秀' UNION ALL
SELECT 'aaaa','2009','b','称职' UNION ALL
SELECT 'aaaa','2008','a','良好' UNION ALL
SELECT 'aaaa','2008','b','称职'--SELECT * FROM [khqkb]-->SQL查询如下:
DECLARE @beginyear INT,@endyear INT,@col VARCHAR(1000)
SELECT @beginyear=2009,@endyear=2006
SELECT @col=ISNULL(@col+',','')+QUOTENAME(@beginyear-number)
FROM [master]..spt_values 
WHERE TYPE='p'
AND number<=ABS(@beginyear-@endyear)
EXEC('
SELECT * 
FROM khqkb 
PIVOT(MAX(khjg) 
FOR nd IN ('+@col+')) AS pvt
')
/*
name       khlb       2009       2008       2007       2006
---------- ---------- ---------- ---------- ---------- ----------
aaaa       a          优秀         良好         NULL       NULL
aaaa       b          称职         称职         NULL       NULL(2 行受影响)
*/

Answer 5

一、行转列
if not object_id('class') is null
 drop table class
go
create table class(sname varchar(10),cname varchar(10),score int)Insert class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 --1、静态方法
select 
    sname,
    [数学]=max(case when cname='数学' then score else 0 end),
    [物理]=max(case when cname='物理' then score else 0 end),
    [英语]=max(case when cname='英语' then score else 0 end),
    [语文]=max(case when cname='语文' then score else 0 end) 
from 
    Class 
group by sname
--2、利用函数
create function GetScore(@sname varchar(10),@cname varchar(10))
returns int
as
begin
declare @score int
set @score=0
select @score=score from class where sname=@sname and cname=@cname
return @score
endselect sname,语文=dbo.GetScore(sname,'语文'),数学=dbo.GetScore(sname,'数学'),
物理=dbo.GetScore(sname,'物理'),英语=dbo.GetScore(sname,'英语')
from class
group by sname
--3、动态
declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(cname)+'=max(case when cname='+quotename(cname,'''')+' then score else 0 end)'
from Class group by cname
select @s
exec('select sname'+@s+' from Class group by sname')
--quotename使函数中的输入成为一个有效的标示符
--4、利用pivot对表进行透视
select * 
from 
    Class 
pivot 
    (max([Score]) for cname in([数学],[物理],[英语],[语文]))b
    
--5、加上总成绩和平均成绩（静态方法）
select *,[数学]+[物理]+[英语]+[语文] 总成绩,([数学]+[物理]+[英语]+[语文])/4 平均成绩
from
(
select 
    sname 姓名,
    [数学]=max(case when cname='数学' then score else 0 end),
    [物理]=max(case when cname='物理' then score else 0 end),
    [英语]=max(case when cname='英语' then score else 0 end),
    [语文]=max(case when cname='语文' then score else 0 end)
from 
    Class 
group by sname) bselect 
    sname 姓名,
    [数学]=max(case when cname='数学' then score else 0 end),
    [物理]=max(case when cname='物理' then score else 0 end),
    [英语]=max(case when cname='英语' then score else 0 end),
    [语文]=max(case when cname='语文' then score else 0 end),
    [总成绩]=sum(score),
    [平均成绩]=avg(score)
from 
    Class 
group by sname--6、加上总成绩和平均成绩（动态方法）
declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score])  from Class group by [Student]')--加多一列(学科平均二、列转行
if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78--1、动态转换
declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from class1'
from syscolumns where ID=object_id('class1') and Name not in('Student')--排除不转换的列
order by Colid
--select @s
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序
--注：null 与其他值做运算时，结果为null
--2、静态转换
select * 
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all 
select [Student],[Course]='物理',[Score]=[物理] from Class union all 
select [Student],[Course]='英语',[Score]=[英语] from Class union all 
select [Student],[Course]='语文',[Score]=[语文] from Class)t 
order by [Student],[Course]--3、2005
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student') 
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select 
    Student,[Course],[Score] 
from 
    Class 
unpivot 
    ([Score] for [Course] in([数学],[物理],[英语],[语文]))b