单行到单列 [英] single row to single column
本文介绍了单行到单列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
输入
姓名raj mani bala ram
输出
名称
raj
mani
bala
ram
input
Name raj mani bala ram
output
Name
raj
mani
bala
ram
推荐答案
如果您使用的是SQL Server,请尝试这个:
DECLARE @TXT_TO_PARSE VARCHAR(MAX)='姓名raj mani bala ram'
;使用SRC_TXT AS(
/ *此CTE用空格替换换行符* /
SELECT REPLACE(REPLACE(@ TXT_TO_PARSE,CHAR(13),''),CHAR (10),'')+''AS TXT
),
/ *以下CTE递归循环遍历字符串中的所有标记* /
TOKENISE AS(
SELECT
CAST(TXT AS VARCHAR(MAX))AS TXT
,CAST(NULL AS VARCHAR( MAX))作为TOKEN
,CAST(1 AS INTEGER)作为PTR
来自SRC_TXT
UNION ALL
SELECT TXT
,CAST(RTRIM(SUTRTRING(TXT,PTR,CHARINDEX('',TXT,PTR + 1)-PTR)))AS VARCHAR(MAX))AS TOKEN
,CAST(CHARINDEX('',TXT,PTR + 1)AS INTeger)AS PTR
来自TOKENISE
WHERE CHARINDEX('',TXT, PTR + 1)-PTR> 0
)
SELECT TOKEN
来自TOKENISE
哪里没有令牌NULL和TOKEN!=''
希望这个工作
If you are using SQL Server, try this:
DECLARE @TXT_TO_PARSE VARCHAR(MAX) = 'Name raj mani bala ram'
; WITH SRC_TXT AS (
/*This CTE replaces line feeds with a Space*/
SELECT REPLACE(REPLACE(@TXT_TO_PARSE,CHAR(13),' '),CHAR(10),' ')+' ' AS TXT
),
/*The Following CTE recursively loops through all the Tokens in the string*/
TOKENISE AS (
SELECT
CAST(TXT AS VARCHAR(MAX)) AS TXT
,CAST(NULL AS VARCHAR(MAX)) AS TOKEN
,CAST(1 AS INTEGER) AS PTR
FROM SRC_TXT
UNION ALL
SELECT TXT
,CAST(RTRIM(LTRIM(SUBSTRING(TXT,PTR,CHARINDEX(' ',TXT,PTR+1)-PTR))) AS VARCHAR(MAX)) AS TOKEN
,CAST(CHARINDEX(' ',TXT,PTR+1) AS INTeger) AS PTR
FROM TOKENISE
WHERE CHARINDEX(' ',TXT,PTR+1)-PTR > 0
)
SELECT TOKEN
FROM TOKENISE
WHERE TOKEN IS NOT NULL AND TOKEN!=' '
Hope this works
尝试使用Pivote表。
http: //stackoverflow.com/questions/12169016/simple-pivot-sample [ ^ ]
Try using Pivote table.
http://stackoverflow.com/questions/12169016/simple-pivot-sample[^]
尝试使用以下链接中给出的拆分功能
http://www.aspdotnet-suresh.com/2013/07/sql- server-split-function-example-in.html [ ^ ]
try with split function given in below link
http://www.aspdotnet-suresh.com/2013/07/sql-server-split-function-example-in.html[^]
这篇关于单行到单列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文