SQL :从包含多语言信息的字符串中分离新列。

Question

我有一个包含2种不同语言信息的数据。

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

CREATE TABLE #TblName (
    JobNum varchar(10)
    ,CommentText nvarchar(max)
)

INSERT INTO #TblName VALUES ('F001234','Vietnamese point-1; Vietnamese point-2; Vietnamese point-3; Vietnamese point-4; Vietnamese point-5;  English point-1; English point-2; English point-3; English point-4; English point-5;')
INSERT INTO #TblName VALUES ('F005678','Vietnamese point-1; English point-2; Vietnamese point-3; English point-1; Vietnamese point-2; English point-3; English point-4')

select * from #TblName

产出如下：

JobNum    CommentText
F001234   Vietnamese point-1; Vietnamese point-2; Vietnamese point-3; Vietnamese point-4; Vietnamese point-5;  English point-1; English point-2; English point-3; English point-4; English point-5;
F005678   Vietnamese point-1; English point-2; Vietnamese point-3; English point-1; Vietnamese point-2; English point-3; English point-4

在SQL查询中是否有一种方法可以获得输出，类似于两列中的每种语言信息：

输出

事先谢谢你的回答..。

Answer 1

这是你问题的答案。有人能建议如何使列内枢轴动态吗？

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

IF OBJECT_ID('tempdb..#tableB') IS NOT NULL
    BEGIN
        DROP TABLE #tableB
    END

CREATE TABLE #TblName (
    JobNum varchar(10)
    ,CommentText nvarchar(max)
)

INSERT INTO #TblName VALUES ('F001234','Vietnamese point-1; Vietnamese point-2; Vietnamese point-3; Vietnamese point-4; Vietnamese point-5;  English point-1; English point-2; English point-3; English point-4; English point-5;')
INSERT INTO #TblName VALUES ('F005678','Vietnamese point-1; English point-2; Vietnamese point-3; English point-1; Vietnamese point-2; English point-3; English point-4')

select * from #TblName

接下来，我将在结果t下面加载一个临时表#tableB

SELECT JobNum, RetVal, SUBSTRING(retval,1,CHARINDEX(' ',retval,0)) Lang 
INTO #tableB 
FROM (
    Select A.JobNum
          ,B.*
    FROM #TblName A
    CROSS APPLY (SELECT  RetVal = LTRIM(RTRIM(B.i.value('(./text())[1]', 'varchar(max)')))
                    From  (
                        SELECT x = CAST('<x>' + REPLACE((SELECT REPLACE(A.CommentText,';','§§Split§§') AS [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')
                        ) as A 
                    CROSS APPLY x.nodes('x') AS B(i)) B
    )t where retval IS NOT NULL

SELECT * from #tableB

最后，这里产生了预期的结果。但是，列名不是动态的。如果找到解决方案，我将更新代码。

SELECT * 
FROM (
    SELECT jobnum,lang, STUFF((
             SELECT ';' + RetVal
                FROM #tableB xx
                WHERE xx.JobNum = xy.JobNum and xx.Lang = xy.Lang
                ORDER BY XX.RetVal
                FOR XML PATH('')
            
             ), 1, 1, '') AS a
             FROM #tableB xy
             GROUP BY JobNum,Lang
    )t
PIVOT (MAX(a) 
    FOR lang IN ([English],[Vietnamese])) piv

Answer 2

WITH 
T AS
(
select JobNum, LTRIM(value) AS V, LEFT(LTRIM(value), CHARINDEX(' ', LTRIM(value)) - 1) AS L
from #TblName
     CROSS APPLY STRING_SPLIT(CommentText, ';')
WHERE LTRIM(value) <> ''
)
SELECT JobNum, 
       CASE WHEN L = 'Vietnamese' THEN RIGHT(V, LEN(V) - CHARINDEX (' ', V)) ELSE NULL END AS Vietnamese,
       CASE WHEN L = 'English' THEN RIGHT(V, LEN(V) - CHARINDEX (' ', V)) ELSE NULL END AS English
FROM   T