Изменение строкового значения с разделителями в запросе выбора sql

Первый CTE — это просто выбор для создания фиктивных записей, вы можете игнорировать это. 2-й CTE является рекурсивным для разделения значений столбца на ^ для каждого идентификатора, 3-й CTE — для объединения разделов, разделенных -, с использованием XML для каждого идентификатора, последний CTE — для объединения всех их в одну строку, разделенную ,. Должен был быть столбец идентификатора для группировки, прежде чем объединять все. Он будет работать с n количеством строк.

;WITH myTable (id, MyColumn)
AS (SELECT 1, '0040~0040~0040~0040^00~00~00~01~05^100~001~010~011~015^00~00~00~01~05'
union ALL SELECT 2, 'b040~0040~0040~0040^b0~00~00~01~05^b00~001~010~011~015^b02~00~00~01~05'
union ALL SELECT 3, 'c040~0040~0040~0040^c0~00~00~01~05^c00~001~010~011~015^c02~00~00~01~05'
union ALL SELECT 4, 'd040~0040~0040~0040^d0~00~00~01~05^d00~001~010~011~015^d02~00~00~01~05'
),
RecursiveCTE (id, sub, startInd, endInd)
AS
(
    SELECT 
        id, 
        convert(varchar, SUBSTRING(MyColumn, 1, CHARINDEX('^', MyColumn ))) as sub
        , 0 AS startInd
        , CHARINDEX('^', MyColumn ) AS endInd
        from mytable
    UNION ALL
        select id,
        convert(varchar, SUBSTRING((SELECT MyColumn FROM myTable WHERE id = dr.id), endInd + 1, endInd - startInd)),
        endInd,
        CHARINDEX('^',  (SELECT MyColumn FROM myTable WHERE id = dr.id), endInd + 1) as endInd
    from RecursiveCTE dr
        where dr.endInd <> 0
),
PerIDSeperatedByDash AS (
select id,
STUFF((
select '-' + SUBSTRING(drinner.sub, 1, CHARINDEX('~', drinner.sub) - 1)
from RecursiveCTE drinner
WHERE (drinner.id = drouter.id)
FOR XML PATH ('')),1,1,'') AS DashSeperated
from RecursiveCTE drouter
group by id)
SELECT DISTINCT STUFF((
SELECT ',' + ds.DashSeperated
FROM PerIDSeperatedByDash ds
ORDER BY id
FOR XML PATH ('')),1,1,'') 
from PerIDSeperatedByDash 

Проблема, которую вы представили, не является легко решаемой, ограничивая себя внутренней частью оператора select. Хотя вы сказали, что нет, но если бы вы знали, сколько «головок» будет в каждой строке, вы могли бы настроить один, сложный и очень длинный оператор выбора (с вашим «строковым значением, имеющим разделители», называемым фубар):

SELECT x,y,z, SUBSTR(foobar, 1, INSTR(foobar, '~') - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^') + 1, INSTR(foobar, '~', INSTR(foobar, '^')) - INSTR(foobar, '^') - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^', 1, 2) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 2)) - INSTR(foobar, '^', 1, 2) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^', 1, 3) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 3)) - INSTR(foobar, '^', 1, 3) - 1) || ',' || SUBSTR(foobar, INSTR(foobar, '~') + 1, INSTR(foobar, '~', 1, 2) - INSTR(foobar, '~', 1, 1) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '~', INSTR(foobar, '^', 1, 1)) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 1)) - INSTR(foobar, '^', 1, 1) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '~', INSTR(foobar, '^', 1, 2)) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 2)) - INSTR(foobar, '^', 1, 2) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '~', INSTR(foobar, '^', 1, 3)) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 3)) - INSTR(foobar, '^', 1, 3) - 1) 
FROM abc

И это получает только первые две «записи», однако он получит каждую «голову», независимо от длины, и его можно расширить, чтобы включить больше записей, но это не сработало бы, если бы было только две записи, а оператор выбора был расширен для пяти записей, что нецелесообразно, поскольку вы сказали, что в строке может быть любое количество записей. Есть способ добиться этого с помощью оператора CASE, но он очень длинный и очень сложный. Вот пример запроса, который может быть тем, что вы ищете:

SELECT x, y, z,
  CASE (LENGTH(SUBSTR(foobar, 1, INSTR(foobar, '^')))-LENGTH(REPLACE(SUBSTR(foobar, 1, INSTR(foobar, '^')), '~', '')) + 1)
    WHEN 1 THEN SUBSTR(foobar, 1, INSTR(foobar, '^') - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^', 1, 1) + 1, INSTR(foobar, '^', 1, 2) - INSTR(foobar, '^', 1, 1) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^', 1, 2) + 1, INSTR(foobar, '^', 1, 3) - INSTR(foobar, '^', 1, 2) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^', 1, 3) + 1, LENGTH(foobar) - INSTR(foobar, '^', 1, 3))
    WHEN 2 THEN SUBSTR(foobar, 1, INSTR(foobar, '~') - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^') + 1, INSTR(foobar, '~', INSTR(foobar, '^')) - INSTR(foobar, '^') - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^', 1, 2) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 2)) - INSTR(foobar, '^', 1, 2) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '^', 1, 3) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 3)) - INSTR(foobar, '^', 1, 3) - 1) || ',' || SUBSTR(foobar, INSTR(foobar, '~') + 1, INSTR(foobar, '^', 1, 1) - INSTR(foobar, '~', 1, 1) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '~', INSTR(foobar, '^', 1, 1)) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 1), 1) - INSTR(foobar, '^', 1, 1) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '~', INSTR(foobar, '^', 1, 2)) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 2)) - INSTR(foobar, '^', 1, 2) - 1) || '-' || SUBSTR(foobar, INSTR(foobar, '~', INSTR(foobar, '^', 1, 3)) + 1, INSTR(foobar, '~', INSTR(foobar, '^', 1, 3)) - INSTR(foobar, '^', 1, 3) - 1) 
    ELSE 'Too many entries: ' || (LENGTH(SUBSTR(foobar, 1, INSTR(foobar, '^')))-LENGTH(REPLACE(SUBSTR(foobar, 1, INSTR(foobar, '^')), '~', '')) + 1) || ' entries given of 2 maximum'
  END AS SAMPLE_OUTPUT
FROM abc;

Приведенный выше запрос (если его расширить до двух записей) упорядочит ваши «головы». Он очень длинный и сложный, но если вы знаете, что длина каждого заголовка будет постоянной (например, всегда иметь длину 4 на запись в «Head1»), тогда код можно сократить, с дополнительным преимуществом более быстрого выполнения. , если значения жестко запрограммированы без INSTR. В приведенном ниже примере предполагается, что длина головы равна 4, 2, 3 и 2, как в ваших примерах данных:

SELECT x, y, z,
  CASE (LENGTH(SUBSTR(foobar, 1, INSTR(foobar, '^')))-LENGTH(REPLACE(SUBSTR(foobar, 1, INSTR(foobar, '^')), '~', '')) + 1)
    WHEN 1 THEN REPLACE(foobar, '^', '-')
    WHEN 2 THEN SUBSTR(foobar, 1, 4) || '-' || SUBSTR(foobar, 11, 2) || '-' || SUBSTR(foobar, 17, 3) || '-' || SUBSTR(foobar, 25, 2) || ',' || SUBSTR(foobar, 6, 4) || '-' || SUBSTR(foobar, 14, 2) || '-' || SUBSTR(foobar, 21, 3) || '-' || SUBSTR(foobar, 28, 2) 
    WHEN 3 THEN SUBSTR(foobar, 1, 4) || '-' || SUBSTR(foobar, 16, 2) || '-' || SUBSTR(foobar, 25, 3) || '-' || SUBSTR(foobar, 37, 2) || ',' || SUBSTR(foobar, 6, 4) || '-' || SUBSTR(foobar, 19, 2) || '-' || SUBSTR(foobar, 29, 3) || '-' || SUBSTR(foobar, 40, 2) || ',' || SUBSTR(foobar, 11, 4) || '-' || SUBSTR(foobar, 22, 2) || '-' || SUBSTR(foobar, 33, 3) || '-' || SUBSTR(foobar, 43, 2)
    ELSE 'Too many entries: ' || (LENGTH(SUBSTR(foobar, 1, INSTR(foobar, '^')))-LENGTH(REPLACE(SUBSTR(foobar, 1, INSTR(foobar, '^')), '~', '')) + 1) || ' entries given of 3 maximum'
  END AS SAMPLE_OUTPUT
FROM abc;

И последнее замечание: я рекомендую использовать псевдоним столбца для оператора case по причинам, которые станут очевидными, если вы попытаетесь протестировать код без него.