Regrouper des données avec CASE

SQL Server intermédiaire

Ginger Grant

Instructor

Modifier des valeurs de colonnes avec CASE

CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

Modifier des valeurs de colonnes avec CASE en T-SQL

SELECT Continent, 
CASE WHEN Continent = 'Europe' or Continent = 'Asia' THEN 'Eurasia'
     ELSE 'Other'
     END AS NewContinent
FROM EconomicIndicators

+-----------+--------------+
|Continent  |NewContinent  |
+-----------+--------------+
|Europe     |Eurasia       |
|Asia       |Eurasia       |
|Americas   |Other         |
...
+-----------+--------------+

Modifier des valeurs de colonnes avec CASE en T-SQL

SELECT Continent, 
CASE WHEN Continent = 'Europe' or Continent = 'Asia' THEN 'Eurasia'
     ELSE Continent 
     END AS NewContinent
FROM EconomicIndicators

+-----------+--------------+
|Continent  |NewContinent  |
+-----------+--------------+
|Europe     |Eurasia       |
|Asia       |Eurasia       |
|Americas   |Americas      |
...
+-----------+--------------+

Créer des groupes de valeurs avec des instructions CASE

-- We are binning the data here into discrete groups
SELECT Country, LifeExp, 
CASE WHEN LifeExp < 30 THEN 1
     WHEN LifeExp > 29 AND LifeExp < 40 THEN 2
     WHEN LifeExp > 39 AND LifeExp < 50 THEN 3
     WHEN LifeExp > 49 AND LifeExp < 60 THEN 4
     ELSE 5
     END AS LifeExpGroup
FROM EconomicIndicators
WHERE Year = 2007

+-----------+--------------+
|LifeExp    |LifeExpGroup  |
+-----------+--------------+
|25         |1             |
|30         |2             |
|65         |5             |
...
+-----------+--------------+

Passons à la pratique !

SQL Server intermédiaire