Sql 行转换为列 以及列转换为行的心得

CREATE TABLE [dbo].[stu](
	[学号] [nvarchar](255) NOT NULL,
	[姓名] [nvarchar](255) NULL,
	[性别] [nvarchar](255) NULL,
	[专业] [nvarchar](255) NULL,
	[院系] [nvarchar](255) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[stu] ([学号], [姓名], [性别], [专业], [院系]) VALUES (N‘111405060432‘, N‘王小明‘, N‘男‘, N‘金融系‘, N‘经济学院‘)
INSERT [dbo].[stu] ([学号], [姓名], [性别], [专业], [院系]) VALUES (N‘07102060215‘, N‘王硕问‘, N‘男‘, N‘材料成型及控制工程‘, N‘材料科学与工程学院‘)
INSERT [dbo].[stu] ([学号], [姓名], [性别], [专业], [院系]) VALUES (N‘07104060407‘, N‘冯静‘, N‘女‘, N‘金融学‘, N‘经济学院‘)
INSERT [dbo].[stu] ([学号], [姓名], [性别], [专业], [院系]) VALUES (N‘07108040122‘, N‘王新哲‘, N‘男‘, N‘环境工程‘, N‘化工与制药学院‘)
INSERT [dbo].[stu] ([学号], [姓名], [性别], [专业], [院系]) VALUES (N‘07110020114‘, N‘刘龙‘, N‘男‘, N‘应用物理学‘, N‘物理与工程学院‘)

--------行值转为列值  使用Case语句    方法一
SELECT  学号
    ,Max(CASE 专业 WHEN ‘金融系‘ THEN  院系 else 院系  END) AS ‘金融系‘
    ,Max(CASE 专业 WHEN ‘材料成型及控制工程‘ THEN 院系  else 院系  END) AS ‘材料成型及控制工程‘
    ,Max(CASE 专业 WHEN ‘金融学‘ THEN 院系  else 院系  END) AS ‘金融学‘
    ,Max(CASE 专业 WHEN ‘环境工程‘ THEN 院系  else 院系  END) AS ‘环境工程‘
    ,Max(CASE 专业 WHEN ‘应用物理学‘ THEN 院系 else 院系  END) AS ‘应用物理学‘
FROM
    stu
GROUP BY 学号
go

------行转换列值   ------    方法二
declare @sql varchar(8000)

select @sql=isnull(@sql+‘,‘,‘‘)+‘ max(case 专业 when ‘‘‘+专业+‘‘‘ then 院系 else 院系  end) [‘+专业+‘]‘

from(select distinct 专业 from stu)as a      

set @sql=‘select 学号 ,‘+@sql+‘ from stu group by 学号‘

exec(@sql)
go

 -----使用isnull()-----
declare @sql varchar(8000)

select @sql=isnull(@sql+‘,‘,‘‘)+ 专业 from stu  group by 专业           

set @sql=‘select * from stu pivot (max(院系) for 专业 in (‘+@sql+‘))a‘

exec(@sql)
go

----------使用pivot --------

select *            -----要选取的列， * 代表选择全部
from  stu            -------从哪个结果集中选出数据
 pivot(
 max(院系) --聚合函数Max表示你需要怎样处理转换后的列的值，是总和(sum)，还是平均(avg)还是min,max等等。
 for 专业 ---- for 专业 就是说将 专业 列的值分别转换成一个个列，也就是“以值变列”。
 in            -------我们只想取其中几个值转换成列，那么怎样取呢？就是在in里面写要取的值
 (材料科学与工程学院,经济学院,化工与制药学院,物理与工程学院,金融学))---
  as b
 go

 -----------列转行 ---------------------
-----使用SQL Server 2005动态SQL
declare @sql nvarchar(4000)

select  @sql=isnull(@sql+‘,‘,‘‘)+quotename(Name)
from  syscolumns
where  ID=object_id(‘stu‘)and Name not in(‘姓名‘,‘学号‘,‘专业‘,‘性别‘)
order by Colid
set @sql=‘select 学号,姓名,[专业],性别 from stu unpivot ([aa] for [bb] in(‘+@sql+‘))b‘
exec(@sql)
go


------方法二------------
select * from
(
 select 学号,姓名,性别,专业=‘金融系‘,院系=‘经济学院‘ from stu

 union all

 select 学号,姓名,性别,专业=‘材料成型及控制工程‘,院系=‘材料科学与工程学院‘ from stu

 union all

 select 学号,姓名,性别,专业=‘金融学‘,院系=‘经济学院‘ from stu

) t  order by 学号
go

----------使用 unpivot----------

select aa,bb from  stu unpivot (aa for  bb  in([院系],[专业])) t