資料庫行轉列、列轉行的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'