sql server 行列转换

创建表：

CREATE TABLE [dbo].[tbl_Student](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [学生ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [学生姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [课程ID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [课程名称] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [成绩] [int] NULL,
    [教师ID] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [教师姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

 

插入数据：

insert into tbl_Student values(
'S3','王五','K4 ','政治',53,'T4','赵老师');
insert into tbl_Student values(
'S1','张三 ','K1 ','数学',61,'T1','张老师');
insert into tbl_Student values(
'S2','李四','K3 ','英语',88,'T3','李老师');
insert into tbl_Student values(
'S1','张三 ','K4 ','政治',77,'T4','赵老师');
insert into tbl_Student values(
'S2','李四','K4 ','政治',67,'T5','周老师');
insert into tbl_Student values(
'S3','王五','K2 ','语文',90,'T2','王老师');
insert into tbl_Student values(
'S3','王五','K1 ','数学',55,'T1','张老师');
insert into tbl_Student values(
'S1','张三 ','K2 ','语文',81,'T2','王老师');
insert into tbl_Student values(
'S4','赵六','K2 ','语文',59,'T1','王老师');
insert into tbl_Student values(
'S1','张三 ','K3 ','英语',37,'T3','李老师');

行专列

方法1：

select 学生姓名,课程ID,成绩,教师ID,教师姓名,
max(case 课程名称 when '政治' then 成绩 else 0 end) 政治, 
max(case 课程名称 when '语文' then 成绩 else 0 end) 语文,
max(case 课程名称 when '数学' then 成绩 else 0 end) 数学,
max(case 课程名称 when '英语' then 成绩 else 0 end) 英语
from tbl_Student group by 学生姓名,课程ID,成绩,教师ID,教师姓名

方法2：

select *
from (select * from tbl_Student) 
a pivot (max(成绩) for 课程名称 in (语文,数学,英语,政治)) b

结果如图：

之所以很多地方为NULL是因为pivot和unpivot会把除 pivot()括号内的字段都作为分组项，所以如果想实现如下图效果。

 

 则需要先做一个子查询或CTE来把相关字段给筛选出来，代码如下（或可以用case when做行转列直接group by 指定字段）

select *
from (select 学生姓名,成绩,课程名称 from tbl_Student) 
a pivot (max(成绩) for 课程名称 in (语文,数学,英语,政治)) b

 

结果如下：

 

　　

 

列转行：（或可以用union all做列传行）

CREATE TABLE [dbo].[tbl_列转行测试](
    [UserID] [int] NULL,
    [UserNo] [int] NULL,
    [A] [int] NULL,
    [B] [int] NULL,
    [C] [int] NULL
) ON [PRIMARY]

insert into [tbl_列转行测试] values(1           ,      1       ,        11   ,       22     ,     33)

select * from [tbl_列转行测试]

SELECT  USERID,USERNO,attribute,value
FROM (select * from tbl_列转行测试)a
  UNPIVOT
  (
    value FOR attribute IN(A, B,C)
  ) AS UPV

 

 

 

 

 

 

 

二、分隔符行列转换

1、行转列以，号为分隔符

 好的 FOR XML PATH就基本介绍到这里吧，更多关于FOR XML的知识请查阅帮助文档！

    接下来我们来看一个FOR XML PATH的应用场景吧！那么开始吧。。。。。。

        二.一个应用场景与FOR XML PATH应用

        首先呢！我们在增加一张学生表，列分别为（stuID,sName,hobby）,stuID代表学生编号，sName代表学生姓名，hobby列存学生的爱好！那么现在表结构如下：

           

        这时，我们的要求是查询学生表，显示所有学生的爱好的结果集，代码如下：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B 

         结果如下:

 分析： 好的，那么我们来分析一下，首先看这句：

SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')

这句是通过FOR XML PATH 将某一姓名如张三的爱好，显示成格式为：“ 爱好1，爱好2，爱好3，”的格式！

那么接着看：

SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby FROM (
SELECT sName,
(SELECT hobby+',' FROM student 
  WHERE sName=A.sName 
  FOR XML PATH('')) AS StuList
FROM student A 
GROUP BY sName
) B  

剩下的代码首先是将表分组，在执行FOR XML PATH 格式化，这时当还没有执行最外层的SELECT时查询出的结构为:

可以看到StuList列里面的数据都会多出一个逗号，这时随外层的语句:SELECT B.sName,LEFT(StuList,LEN(StuList)-1) as hobby  就是来去掉逗号，并赋予有意义的列明！

 

 

2.列转行

 

----------------------------------------------------------------

--> 测试数据[huang]

if object_id( '[huang]' )  is  not  null  drop  table  [huang]

go 

create  table  [huang]([a] nvarchar(4),[b] nvarchar(10))

insert  [huang]

select  'X1' , '1,4,8'  union  all

select  'X2' , '2'  union  all

select  'X3' , '3,6'  union  all

select  'X4' , '7'  union  all

select  'X5' , '5'

--------------生成数据--------------------------

 

select

    

     a.[a], 

     SUBSTRING ([b],number,CHARINDEX( ',' ,[b]+ ',' ,number)-number)  as  [b] 

from

     [huang] a,master..spt_values 

where

     number >=1  and  number<=len([b])  

     and  type= 'p' 

     and  substring ( ',' +[b],number,1)= ','

----------------结果----------------------------

/* 

a    b

---- ----------

X1   1

X1   4

X1   8

X2   2

X3   3

X3   6

X4   7

X5   5

*/

 

 

经典参考文章：https://blog.csdn.net/ml1990s/article/details/16953999

参考文章：https://www.cnblogs.com/doubleliang/archive/2011/07/06/2098775.html

转载于:https://www.cnblogs.com/gered/p/8696473.html