SQLServer列转行SQL语句完整示例

你的名字 2024-02-05 19:57 49阅读 0赞

## 列转行 ##

原始数据：  
![在这里插入图片描述][30f0c38d35de46e1844f85e245ffa41a.png]  
效果：  
![在这里插入图片描述][f464e8fe22da49038a32eb58d52746fa.png]

### 完整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

[30f0c38d35de46e1844f85e245ffa41a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/935a5ac5843c4f82a4b6d2241909f448.png
[f464e8fe22da49038a32eb58d52746fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/ea7756c1f9bf44bbbf352e7a64e23841.png