SQLServer列转行SQL语句完整示例

列转行

原始数据：

效果：

完整SQL语句

建表

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RowColumnConversion](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Advanced] [int] NULL,
[Framework] [int] NULL,
[UpperComputer] [int] NULL,
[Web] [int] NULL,
[FullStack] [nchar](10) NULL,
CONSTRAINT [PK_RowColumnConversion] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

列转行方式一

select row_number() over(order by id desc) as id,[name],t.course,t.score from(
select id,[name],course='高级班',score=[Advanced] from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='架构班',score=Framework from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='上位机班',score=UpperComputer from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='Web前端',score=Web from [ZhaoxiEdu].[dbo].[RowColumnConversion]
union all
select id,[name],course='全栈班',score=FullStack from [ZhaoxiEdu].[dbo].[RowColumnConversion]
) t where score is not null order by id asc

列转行方式二

with tt as(
select id, [Name],course='高级班',score=[Advanced] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='架构班',score=[Framework] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='上位机',score=[UpperComputer] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='Web前端',score=[Web] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion]
Union all
select id,[Name],course='全栈班',score=[FullStack] FROM [ZhaoxiEdu].[dbo].[RowColumnConversion])
select * from tt where score<>0 order by [Name] desc