首页 > 代码库 > MSSQLServer 纵向表转横向表 横向表转纵向表 行转列 列转行

MSSQLServer 纵向表转横向表 横向表转纵向表 行转列 列转行

MSSQLServer 纵向表转横向表  横向表转纵向表

建表语句及插入数据语句:

CREATE TABLE Test_y(
    [Name] [nchar](10) NULL,
    [Course] [nchar](10) NULL,
    [Grade] [int] NULL
) 
insert into Test_y values (张三,语文,75);
insert into Test_y values (张三,数学,80);
insert into Test_y values (张三,英语,90);
insert into Test_y values (李四,语文,90);
insert into Test_y values (李四,数学,70);
insert into Test_y values (李四,英语,80);

CREATE TABLE Test_x(
    [Name] [nchar](10) NULL,
    [语文] [nchar](10) NULL,
    [数学] [nchar](10) NULL,
    [英语] [nchar](10) NULL
)
insert into Test_x values(张三,75,80,90);
insert into Test_x values(李四,90,70,80);

纵向表转横向表效果展示:

纵向表转横向表 sql 语句如下:

方法一:

select * from Test_y;
select Name,
    sum(case Course when 语文 then Grade else 0 end) as 语文,
    sum(case Course when 数学 then Grade else 0 end) as 数学,
    sum(case Course when 英语 then Grade else 0 end) as 英语
    from Test_y group by Name;

方法二:

select * From Test_y
    pivot(sum(Grade) for Course IN ([语文],[数学],[英语]))
    AS Test_x;

横向表转纵向表效果展示:

横向表转纵向表 sql  语句如下:

方法一:

select * from Test_x;
select Name,语文 as Course,语文 as Grade
    from Test_x union all
select Name,数学 as Course,数学 as Grade
    from Test_x union all
select Name,英语 as Course,英语 as Grade
    from Test_x  order by Name desc;

方法二:

select Name,Course,Grade from Test_x 
unpivot(Grade for Course in([数学],[英语],[语文]))as Test_y

最后谢谢 @漠北水獭 的提示。

新手学习,高手忽略不计即可;

.net技术交流群:70895254