sql 行转列 列转行

--创建表
CREATE TABLE [dbo].[UserInfo](
    [U_id] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nchar](10) NULL,
    [PassWord] [nchar](10) NULL,
 CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED 
(
    [U_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

select * from TEST_TB_GRADE

--行转列
select USER_NAME,COUNT(*),MAX(case course when '数学' then score else 0 end) as '数学',
MAX(case  course when '语文' then Score else 0 end) as '语文' 
,MAX(case  course when '英语' then Score else 0 end) as '英语',
AVG(score) from TEST_TB_GRADE group by USER_NAME
order by USER_NAME

--创建表
CREATE TABLE [dbo].[UserInfo1](
    [U_id] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nchar](10) NULL,
    [PassWord] [nchar](10) NULL,
 CONSTRAINT [PK_UserInfo1] PRIMARY KEY CLUSTERED 
(
    [U_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

select * from TEST_TB_GRADE2
--插入数据
--insert into TEST_TB_GRADE2 values('张三',59,88,99)
--insert into TEST_TB_GRADE2 values('李四',69,78,89)
--insert into TEST_TB_GRADE2 values('王五',70,88,79)
--列转行
select * from TEST_TB_GRADE2

select username,'语文' crouse,CN_score as Score from TEST_TB_GRADE2
union 
select username,'数学' crouse,MATH_score as Score from TEST_TB_GRADE2
union
select username,'英语' crouse,En_score as Score from TEST_TB_GRADE2