Now there is a table with the following structure in the database:
-------------------------------------------------- ----------------
City | Date | Temperature | --------------------------------------
Changsha | 2004-12-21 | 23 | ---------------------------------------
Xiangtan | 2004-12-21 | 28 | -------------------------------------
Zhuzhou | 2004-12-21 | 26 | ---------------------------------------------------------------------------------------------------------------------------------------------------------
Changsha | 2004-12-22 | 27 | -------------------------------------
Xiangtan | 2004-12-22 | 25 | -------------------------------------
Zhuzhou | 2004-12-22 | 22 | ---------------------------------------------------------------------------------------------------------------
The query results are shown as:
-------------------------------------------------- ---------------
Date | Changsha | Xiangtan | Zhuzhou |
-------------------------------------------------- ---------------
2004-12-21 | 23 | 28 | 26 | ----------------------------------
2004-12-22 | 27 | 25 | 22 | ----------------------------------
If you know the number of cities
SELECT date,
[Changsha] = Max (Case City WHEN 'Changsha' Then Temperature ELSE 0 End),
[Xiangtan] = Max (Case City When 'Xiangtan' Then Temperature ELSE 0 End),
[Zhuzhou] = Max (Case City when 'Zhuzhou' Then Temperature ELSE 0 End)
FROM table
GROUP BY date
ORDER BY date
- If you don't know if you are a few cities, use the following second statement
Delcare @s varchar (8000)
Set @ s = 'SELECT date'
SELECT @ s = @ s ', [' City '] = Max (Case City WHEN' ' City ' '' Ten Temperature ELSE 0 End) '
FROM table Group by city
Set @ s = @ s 'from table group by date'
EXEC (@S)
The use of Max is mainly to use Group by
From 9CBS (LSXAA (Xiao Li Pencil Knife), etc.)
