sql server-sql分解值查询

Question

我有这些值：

Numbers 
4651230913
4651278185
4651501024
4651530913
4651543447
QCIM1E123942
4651546422
QCIM1E123946
4651558787
QCIM1E123948
4651574839

现在我只想创建两列，我的输出将是：

Numbers       QC Value
4651230913    null
4651278185    null
4651501024    null
4651530913    null
4651543447    QCIM1E123942
4651546422    QCIM1E123946
4651558787    QCIM1E123948
4651574839    null

Answer 1

我想你想要这样的东西：

DECLARE @Numbers TABLE(n VARCHAR(20))
INSERT INTO @Numbers VALUES
('QCI111111'),
('4651230913'),
('4651278185'),
('4651501024'),
('4651530913'),
('4651543447'),
('QCIM1E123942'),
('QCIM1E123943'),
('4651546422'),
('QCIM1E123946'),
('4651558787'),
('QCIM1E123948'),
('4651574839')
;WITH cte AS(SELECT *, 
                    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn 
             FROM @Numbers)
SELECT CASE WHEN c.n LIKE 'QCI%' THEN NULL ELSE c.n END AS [Numbers], oa.n AS [QC Value] 
FROM cte c
OUTER APPLY(SELECT STUFF((SELECT ', ' + ci.n FROM cte ci 
            WHERE ci.rn >= c.rn AND 
                  ci.n LIKE 'QCI%' AND 
                  ci.rn < (SELECT MIN(rn) FROM cte cm 
                           WHERE cm.rn > c.rn + 1 AND cm.n NOT LIKE 'QCI%') AND
                  ((SELECT n FROM cte WHERE rn = c.rn + 1) LIKE 'QCI%' OR (c.n LIKE 'QCI%' AND c.rn = 1))
            FOR XML PATH('')), 1, 2, '') AS n) oa
WHERE c.n NOT LIKE 'QCI%' OR (c.n LIKE 'QCI%' AND c.rn = 1)

输出：

Numbers     QC Value
NULL        QCI111111
4651230913  NULL
4651278185  NULL
4651501024  NULL
4651530913  NULL
4651543447  QCIM1E123942, QCIM1E123943
4651546422  QCIM1E123946
4651558787  QCIM1E123948
4651574839  NULL

将ORDER BY (SELECT NULL)更改为表中保证排序的适当列。