Exibir status dentro de um select.

Question

Ola pessoal microsoft

Preciso de um help, pois eu não tenho noção de como resolver esse problema.

Selecione os três primeiros colocados da lista com a frase inicial Podium: e também, os dois últimos times que serão rebaixados para série B com a frase inicial Demoted:

SELECT TEAM FROM LEAGUE WHERE 
POSITION <= 3 OR POSITION >= 14;

A logica até eu entendo,  queria saber como coloca um texto (Podium, e Demonted) dentro do select name;

Esquema

league

Coluna	Tipo
position (PK)	integer
team	varchar

Tabelas

league

position	team
1	The Quack Bats
2	The Responsible Hornets
3	The Bawdy Dolphins
4	The Abstracted Sharks
5	The Nervous Zebras
6	The Oafish Owls
7	The Unequaled Bison
8	The Keen Kangaroos
9	The Left Nightingales
10	The Terrific Elks
11	The Lumpy Frogs
12	The Swift Buffalo
13	The Big Chargers
14	The Rough Robins
15	The Silver Crocs

Exemplo de Saída

name

Podium: The Quack Bats

Podium: The Responsible Hornets

Podium: The Bawdy Dolphins

Demoted: The Rough Robins

Demoted: The Silver Crocs

INSERTS E TABELA PARA TESTE

CREATE TABLE LEAGUE(
POSITION NUMERIC PRIMARY KEY,
TEAM VARCHAR (255)
);

INSERT INTO LEAGUE (POSITION, TEAM) VALUES (1, 'The Quack Bats');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (2, 'The Responsible Hornets');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (3, 'The Bawdy Dolphins');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (4, 'The Abstracted Sharks');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (5, 'The Nervous Zebras');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (6, 'The Oafish Owls');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (7, 'The Unequaled Bison');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (8, 'The Keen Kangaroos');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (9, 'The Left Nightingales');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (10, 'The Terrific Elks');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (11, 'The Lumpy Frogs');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (12, 'The Swift Buffalo');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (13, 'The Big Chargers');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (14, 'The Rough Robins');
INSERT INTO LEAGUE (POSITION, TEAM) VALUES (15, 'The Silver Crocs');

Answer 1

Deleted

Answer 2

Coloquei no topo do tópico.

Answer 3

É possível utilizar o CASE para fazer isso:

SELECT CASE 
         WHEN POSITION <= 3 THEN 'Podium: '
         ELSE 'Demoted: '
       END + TEAM  AS Name
FROM LEAGUE 
WHERE POSITION <= 3 OR POSITION >= 14;

Caso queira um exemplo mais dinâmico:

;WITH CTA
AS (
  SELECT COUNT(1) AS QT
  FROM LEAGUE 
)

SELECT position
      ,CASE WHEN position <= 3 THEN 'Podium: '
            ELSE 'Demoted: '
       END + league.team AS name
FROM LEAGUE
     CROSS APPLY CTA
WHERE league.position <= 3 
   OR league.position >= CTA.QT - 1

Answer 4

Esse exemplo não funcionou comigo, deve ser porque eu estou fazendo os scripts no Oracle 11g.

Answer 5

Este é um comando eu montei com base no SQL Server, qual comando não está funcionando o primeiro ou o segundo? O oracle parece ter a mesma funcionalidade tratando-se do case.

Answer 6

Deleted