sql列转行

本文介绍了一种使用SQL实现交叉表的方法,包括列数固定的交叉表实现方式以及列数不确定时如何通过动态SQL来构建交叉表。适用于需要将数据转换为特定格式报表的场景。
CREATE TABLE [Test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Source] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO

INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'语文',60)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'数学',70)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'英语',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'数学',75)
INSERT INTO [test] ([name],[subject],[Source]) values (N'王五',N'语文',57)
INSERT INTO [test] ([name],[subject],[Source]) values (N'李四',N'语文',80)
INSERT INTO [test] ([name],[subject],[Source]) values (N'张三',N'英语',100)
Go

--交叉表语句的实现:
--用于:交叉表的列数是确定的
select name,sum(case subject when '数学' then source else 0 end) as '数学',
sum(case subject when '英语' then source else 0 end) as '英语',
sum(case subject when '语文' then source else 0 end) as '语文' 
from test 
group by name

--用于:交叉表的列数是不确定的
declare @sql varchar(8000)

set @sql = 'select name,'
select @sql = @sql + 'sum(case subject when '''+subject+''' 
then source else 0 end) as '''+subject+''','
from (select distinct subject from test) as a 
select @sql = left(@sql,len(@sql)-1) + ' from test group by name'
exec(@sql)
go

 

 

--用于:交叉表的列数是不确定的
declare @sql varchar(8000)
set @sql = 'select id,'
select @sql = @sql + '(case subject when '''+subject+''' 
then source else null end) as '''+subject+''','
from (select distinct subject from test) as a 
select @sql = left(@sql,len(@sql)-1) + ' from test '
exec(@sql)
go

 

SQL中,将数据转换为数据的过程通常称为**转行**(也称为反透视)。这种转换常用于将宽表结构转换为长表结构,以便进数据分析或报表生成。以下是几种常见的实现方法,适用于不同的数据库系统。 ### 1. 使用 `UNION ALL` 实现转行 这是最基础、最通用的方法,适用于几乎所有支持 `UNION` 操作的数据库系统(如 SQL Server、MySQL、Oracle 等)。 假设有一个成绩表 `Score`,其结构如下: | Name | 数学 | 语文 | 英文 | |-------|------|------|------| | 张三 | 90 | 85 | 95 | | 李四 | 88 | 92 | 89 | 要将这三门课程的成绩转换为数据,可以使用如下 SQL 语句: ```sql SELECT Name, '数学' AS Subject, 数学 AS Score FROM Score UNION ALL SELECT Name, '语文' AS Subject, 语文 AS Score FROM Score UNION ALL SELECT Name, '英文' AS Subject, 英文 AS Score FROM Score ORDER BY Name; ``` 该查询将生成如下结果: | Name | Subject | Score | |-------|---------|-------| | 张三 | 数学 | 90 | | 张三 | 语文 | 85 | | 张三 | 英文 | 95 | | 李四 | 数学 | 88 | | 李四 | 语文 | 92 | | 李四 | 英文 | 89 | 该方法在 SQL Server 中被广泛使用,且可以结合 `CASE` 语句对排序进控制 [^3]。 --- ### 2. 使用 `CROSS APPLY`(适用于 SQL Server) `CROSS APPLY` 是 SQL Server 提供的一种表值函数调用机制,也可以用于转行操作。 ```sql SELECT Name, Subject, Score FROM Score CROSS APPLY ( VALUES ('数学', 数学), ('语文', 语文), ('英文', 英文) ) AS Subjects(Subject, Score) ORDER BY Name; ``` 这种方法语法简洁,执效率较高,适用于多个的转换场景。 --- ### 3. 使用 `UNPIVOT`(适用于 SQL Server 和 Oracle) `UNPIVOT` 是 SQL Server 提供的专门用于转行的语法结构。 ```sql SELECT Name, Subject, Score FROM Score UNPIVOT ( Score FOR Subject IN (数学, 语文, 英文) ) AS Unpvt ORDER BY Name; ``` 该方法结构清晰,适合字段数量较多的情况,但不支持聚合操作。 --- ### 4. 使用 `LISTAGG` 和 `REGEXP_SUBSTR` 实现转行(适用于 Oracle) 如果原始数据中存在多个,且每包含多个值(如逗号分隔字符串),可以先使用 `LISTAGG` 合,再通过正则表达式拆分实现转行。 例如,先使用 `LISTAGG` 合城市信息 [^2],再通过 `REGEXP_SUBSTR` 拆分字符串实现数据输出。 ```sql WITH temp AS ( SELECT 'China' nation, 'Guangzhou' city FROM dual UNION ALL SELECT 'China', 'Shanghai' FROM dual UNION ALL SELECT 'China', 'Beijing' FROM dual ) SELECT nation, REGEXP_SUBSTR(cities, '[^,]+', 1, LEVEL) AS city FROM ( SELECT nation, LISTAGG(city, ',') WITHIN GROUP (ORDER BY city) AS cities FROM temp GROUP BY nation ) CONNECT BY LEVEL <= REGEXP_COUNT(cities, ',') + 1; ``` --- ### 5. 使用 `json_each_table`(适用于 PostgreSQL) PostgreSQL 提供了 JSON 函数来处理转行问题,尤其是当值存储为 JSON 对象时。 ```sql SELECT id, key AS subject, value::int AS score FROM your_table, LATERAL json_each_text(to_jsonb(your_table) - 'id'); ``` 该方法适用于动态结构,且能够处理复杂的嵌套数据。 --- ### 总结 | 方法 | 适用数据库 | 特点 | |--------------|------------------|----------------------------------| | `UNION ALL` | 所有支持 SQL 的数据库 | 通用性强,但写法繁琐 | | `CROSS APPLY`| SQL Server | 语法简洁,性能较好 | | `UNPIVOT` | SQL Server、Oracle | 专为转行设计,结构清晰 | | `LISTAGG` + 正则 | Oracle | 适用于字符串拼接后拆分的场景 | | `JSON` 函数 | PostgreSQL | 适用于 JSON 类型字段的动态处理 | 根据具体的数据库类型和数据结构,可以选择最适合的转行方法。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值