显示奇数行和偶数行到两个单独的列中

Question

但是给定的要求是:

a~b~c~d~e~f

和O/p类似:

a b
c d 
e f

这里a, e是奇数，c是偶数。所以我试图使用这个查询使用string_split()来分离这个符号"~"。有人能帮忙吗?

在这个查询中，我没有得到奇数甚至单独的列输出:

select 
C.CID, c.nunique, 
Row Number() OVER (ORDER BY C.CID) as RowNum,
CASE 
WHEN (ROW Number () OVER(ORDER BY c.CID)) % 2 = 0 THEN s.value 
WHEN (Row Number() OVER (ORDER BY c.CID)) % 2 = 1 THEN s.value
ELSE " invalid"
END
from Candidate c
cross apply STRING SPLIT(applist,~) s

Answer 1

答案:

您需要使用拆分器拆分存储的序列以获得子字符串和子字符串的顺序。基于JSON的方法在这里可能会有所帮助—简单地将文本转换为有效的JSON数组(a~b~c~d~e~f到["a","b","c","d","e","f"]')并使用OPENJSON()解析该数组。OPENJSON()调用的结果是一个包含key、value和type列的表，如果是JSON数组，key列包含数组中每个项目的基于0的索引:

SELECT c.cid, j.odd, j.even
FROM (VALUES (1, 'a~b~c~d~e~f')) c (cid, applist)
CROSS APPLY (
SELECT 
MIN(CASE WHEN CONVERT(int, [key]) % 2 = 0 THEN [value] END) AS [odd],
MIN(CASE WHEN CONVERT(int, [key]) % 2 = 1 THEN [value] END) AS [even]
FROM OPENJSON(CONCAT('["', REPLACE(c.applist, '~', '","'), '"]'))
GROUP BY CONVERT(int, [key]) / 2
) j

结果:

cid odd even
1   a   b
1   c   d
1   e   f

更新:

如果基于json的方法不是一个选项，递归CTE可能会有所帮助:

CREATE TABLE Candidate (cid int, applist varchar(max))
INSERT INTO Candidate (cid, applist) 
VALUES (1, 'a~b~c~d~e~f'), (2, 'g~h~i~i')
;WITH rCTE AS (
SELECT 
c.cid,
LEFT(CONCAT(c.applist, '~'), CHARINDEX('~', CONCAT(c.applist, '~')) - 1) AS substring,
STUFF(CONCAT(c.applist, '~'), 1, CHARINDEX('~', CONCAT(c.applist, '~')), '') AS remaining,
0 AS position
FROM Candidate c 
UNION ALL
SELECT 
r.cid,
LEFT(r.remaining, CHARINDEX('~', r.remaining) - 1),
STUFF(r.remaining, 1, CHARINDEX('~', r.remaining), ''),
r.position + 1
FROM rCTE r
WHERE LEN(r.remaining) > 0
)
SELECT
cid, 
MIN(CASE WHEN position % 2 = 0 THEN [substring] END) AS [odd],
MIN(CASE WHEN position % 2 = 1 THEN [substring] END) AS [even]
FROM rCTE
GROUP BY cid, position / 2
OPTION (MAXRECURSION 0);

UsingSTRING_SPLIT():

注意，虽然STRING_SPLIT()是一个选项，但要小心使用这个函数，因为正如文档中提到的，输出行可以是任意顺序，的顺序不能保证与输入字符串中子字符串的顺序匹配。

SELECT 
c.cid, 
MIN(CASE WHEN rn % 2 = 0 THEN [value] END) AS [odd],
MIN(CASE WHEN rn % 2 = 1 THEN [value] END) AS [even]
FROM (VALUES 
(1, 'a~b~c~d~e~f'),
(2, 'g~h~i~i')
) c (cid, applist)
CROSS APPLY (
SELECT [value], ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 rn
FROM STRING_SPLIT(c.applist, '~')
) s
GROUP BY c.cid, s.rn / 2