数据库行转列、列转行的Sql语句总结

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'