Representing sql data for various cities based on year column

Question

I have a table which consists of city data and temperature of multiple years.

|-----------------------|
| Year  | City  | Temp  |
|-----------------------|
| 2000  | City1 | 25    |
| 2000  | City2 | 29    |
| 2000  | City3 | 35    |
| 2001  | City1 | 17    |
| 2002  | City2 | 28    |
| 2003  | City3 | 19    |
|-----------------------|

I want to represent data in the following way.

|-------------------------------|
| Year  | City1 | City2 | City3 |
|-------------------------------|
| 2000  | 25    | 29    | 35    |
| 2001  | 17    | 28    | 19    |
|-------------------------------|

Shall I represent each city each with a sub-table using the when clause and then join it or is there another simple way?

With that many cities this will be easier to deal with in your application layer. — Nick, Mar 29 '20 at 06:43
What if I need data for only particular cities, for example, 3 in this case. Then what can be a better solution? PS, I have edited the question. — Bhavesh Parvatkar, Mar 29 '20 at 06:45
If you only need a few cities, using conditional aggregation as shown in @CaiusJard answer will work fine. — Nick, Mar 29 '20 at 06:46
Then you can use the technique in my answer, but if you want a different 3 cities every time you'll have to run the report differently. I'll make an edit — Caius Jard, Mar 29 '20 at 06:46
@nick conditional agg of 100 cities "will work fine", it'll just be a pain in the arse to write. Your comment implies that an increasing number of cities will cause problems, and that isn't really the case — Caius Jard, Mar 29 '20 at 06:48
@CaiusJard Sure, it will work. I just believe that for many cities it will be *much* easier to deal with this in the application layer. — Nick, Mar 29 '20 at 06:54
Completely agree with that. Every chart package that has a cross tab report should be able to do it — Caius Jard, Mar 29 '20 at 06:55
Seriously consider handling issues of data display in application code — Strawberry, Mar 29 '20 at 07:48

Caius Jard · Accepted Answer · 2020-03-29T06:51:56.640

You can do that way, sure or you can look at doing this, typically called conditional aggregation:

SELECT
  year,
  MAX(CASE WHEN city = 'City1' THEN temp END) as City1,
  MAX(CASE WHEN city = 'City2' THEN temp END) as City2,
  MAX(CASE WHEN city = 'City3' THEN temp END) as City3
FROM t
GROUP BY year

If you run this you'll see how it works: the city is first spread out across many columns:

SELECT
  year,
  CASE WHEN city = 'City1' THEN temp END as City1,
  CASE WHEN city = 'City2' THEN temp END as City2,
  CASE WHEN city = 'City3' THEN temp End as City3
FROM t

Then when everything is grouped, the MAX function will ignore nulls and only pick the cells with data in, which is one per column

If you had multiple temp recordings per city per year you could perhaps use AVG instead, to get the average of them, or MIN, MAX etc. Quite a flexible technique

Info you're getting a front end to give up to 3 cities then you can have parameters for the names:

SELECT
  year,
  MAX(CASE WHEN city = @c1 THEN temp END) as City1,
  MAX(CASE WHEN city = @c2 THEN temp END) as City2,
  MAX(CASE WHEN city = @c3 THEN temp END) as City3
FROM t
WHERE City IN (@c1, @c2, @c3)
GROUP BY year

You cannot (nor should you) parameterize the names of the columns though. Your front end will have to put the right city name on the report when it pulls the data, like this pseudocode:

labelCity1.Text = "Paris"
labelTemp1.Text = queryResult.GetInt("city1")
...

There's around 100 cities so this is going to get unwieldy... — Nick, Mar 29 '20 at 06:43
Maybe, but it can also be programmatically generated. Most "pivot in the db" approaches are a bit unwieldy at that number of columns! I agree with your comment that it should be dealt with by the front end — Caius Jard, Mar 29 '20 at 06:44

Representing sql data for various cities based on year column

1 Answers1