数据库行转列、列转行的Sql语句总结_sql语句列项内容怎么分开到列-优快云博客

本文链接：https://blog.youkuaiyun.com/qq243348167/article/details/114276556

本文介绍如何在SqlServer和Oracle数据库中实现数据从多行到单行字符串及从单行字符串到多行的转换方法。包括使用FORXML PATH、STUFF、CONVERT、OUTER APPLY、REGEXP_SUBSTR等函数进行数据格式转换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SqlServer多行转字符串

原数据

结果数据库

通过FOR XML PATH把skill按-拼接在一起（-缠绕-寄生-蛛网束缚），然后使用STUFF函数把第一个-符号去掉。

SQL Code

select A.* 
into #result
from
(
	select '唐三' as name,'缠绕' as skill
	union all
	select '唐三' as name,'寄生' as skill
	union all
	select '唐三' as name,'蛛网束缚' as skill
	union all
	select '小舞' as name,'爆杀八段摔' as skill
	union all
	select '小舞' as name,'无敌金身' as skill
) A

SELECT name,
STUFF(
(SELECT '-'+ A1.skill FROM #result A1 WHERE A1.name=A.name FOR XML PATH(''))
,1,1,''
) AS skill
FROM #result A
GROUP BY name
order by name

drop table #result

SqlServer字符串多行转

原数据

结果数据库

通过CONVERT(xml,'<root><v>' + REPLACE(A.skill, '-', '</v><v>') + '</v></root>')把skill转换成xml文档，再通过OUTER APPLY把xml转成多行，并通过A.skill.nodes('/root/v') N(v)取到xml中拆分好的节点数据。

SQL Code

select A.* 
into #result
from
(
	select '唐三' as name,'缠绕-寄生-蛛网束缚' as skill
	union all
	select '小舞' as name,'爆杀八段摔-无敌金身' as skill
) A

select A.name,B.skill from
(
select A.name,skill=CONVERT(xml,'<root><v>' + REPLACE(A.skill, '-', '</v><v>') + '</v></root>')
from #result A
)A
OUTER APPLY(
SELECT skill = N.v.value('.', 'varchar(100)') FROM A.skill.nodes('/root/v') N(v)
)B

drop table #result

Oracle字符串多行转

原数据

结果数据库

SQL Code

select distinct * from (
select  name,regexp_substr(A.skill, '[^-]+', 1, Level,'i') skill
  from (
  select '唐三' as name,'缠绕-寄生-蛛网束缚' as skill from dual
  union all
  select '小舞' as name,'爆杀八段摔-无敌金身' as skill from dual
  ) A
connect by Level <= LENGTH(A.skill) - LENGTH(REGEXP_REPLACE(A.skill, '-', '')) + 1) order by name;

SqlServer查询当前库所有存储是否包含某个表

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%表名%'
AND ROUTINE_TYPE='PROCEDURE'

Oracle树形结构查询

select A.* from dye_app_datadictionary A
START WITH
A.PARENTID in(select id from dye_app_datadictionary where code='10.04') 
CONNECT BY PRIOR ID = PARENTID
order by code