SQL : Separate new columns from a string which contains multiple languages information-CodePudding

I have a data which contain 2 different language information.

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

and the output is as below:

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

is there a way in SQL query to get output like in 2 different columns for each language information:

ouput

Thanks in advance to any answer...

CodePudding user response：

Here is the answer for your question. Can anyone suggest how to make columns inside pivot dynamic?

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

Next I am loading below result t a temporary table #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

Finally, expected result is generated here. However, the column names are not dynamic. I will update the code if I find a solution for it.

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

CodePudding user response：

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