Объединение нескольких строк ответа SQL в одну строку и переименование значений

У меня есть запрос, например...

SELECT a.order_number, b.state
from tableone a
tabletwo b
WHERE
1=1
AND a.id = b.id
AND a.order_number = '12345';

Это выводит:

order_number    state
12345             CA
12345             AZ
12345             NY

Я смотрю вместо того, чтобы возвращать объединенную строку, поэтому я могу сохранить этот результат в одной строке.

order_number       state
12345                 CA,AZ,NY

Итак, я построил это:

SELECT
a.order_number, 
LISTAGG(b.state, ',') WITHIN GROUP (ORDER BY a.order_number) AS states
from tableone a
tabletwo b
WHERE
1=1
AND a.id = b.id
AND a.order_number = '12345'
GROUP BY a.order_number;

Теперь - мой вопрос - как мне присвоить значения этим состояниям? Их всего 3 — CA, AZ и NY, где CA = 1, AZ = 2 и NY = 3. Я либо должен выполнить эту логику, чтобы синхронизироваться с другим приложением, которое использует эти числовые значения для представления состояний здесь, в SQL или в другой системе на бэкенде, и я бы предпочел сделать это здесь, если это возможно...

Я бы хотел, чтобы мой вывод выглядел так:

order_number       state
12345                 1,2,3

sql oracle listagg
person Brian Powell    schedule 09.08.2018    source источник
comment
CA всегда 1, AZ всегда 2 и NY всегда 3. Если NY существует, я хочу заменить его на 3. Если CA существует, я хочу заменить его на 1.   -  person Brian Powell    schedule 09.08.2018

Ответы (1)


arrow_upward
1
arrow_downward

Если вам нужно жестко закодировать преобразование и нет таблицы поиска для преобразования кодов состояний в числа, вы можете просто использовать выражение case:

CASE b.state WHEN 'CA' THEN 1 WHEN 'AZ' THEN 2 WHEN 'NY' THEN 3 END

который вы можете использовать внутри вызова LISTAGG():

SELECT
  a.order_number, 
  LISTAGG(CASE b.state WHEN 'CA' THEN 1 WHEN 'AZ' THEN 2 WHEN 'NY' THEN 3 END, ',')
    WITHIN GROUP (ORDER BY a.order_number) AS states
FROM tableone a
JOIN tabletwo b
ON a.id = b.id
WHERE
1=1
AND a.order_number = '12345'
GROUP BY a.order_number;    

ORDER_NUMBER STATES              
------------ --------------------
12345        1,2,3

Между прочим, я позволил себе переключиться на правильный синтаксис соединения. Кроме того, предложение order by на данный момент ничего не делает, поскольку вы группируете по этому значению, поэтому внутри группы оно всегда одинаково; если порядок имеет значение, вам нужно изменить это - возможно, на то же выражение case.

person Alex Poole    schedule 09.08.2018