CODEWITHSUNDEEP
mysqlpivotview
sradhakrishna
sradhakrishna

Reputation: 99

Column values in a table as columns in a view

I have a table thus:

CityID     ParamName      ParamValue
------    -----------     ----------
  1       Temperature        23    
  1       Humidity           56
  2       Temperature        27
  2       Humidity           49

I'd like to create a view, which would like like this:

CityID    Temperature     Humidity
------    -----------     --------
  1          23              56
  2          27              49

My database engine is MySQL.

Can someone please help me define such a view? Anyhelp will be greatly appreciated.

Upvotes: 0

Views: 80

Answers (2)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

something like 

select someTable.CityID,temps.ParamValue as Temperature,Hums.ParamValue as Humidity
From SomeTable
inner join someTable temps 
On someTable.CityID = temps.CityID and temps.ParamName = 'Temperature'
inner join SomeTable hums  
On someTable.CityID = hums.CityID and hums.ParamName = 'Humidity'

Upvotes: 0

Taryn
Taryn

Reputation: 247720

MySQL does not have a PIVOT function so you will need to you a CASE statement:

SELECT CityId,
    SUM(CASE WHEN ParamName = 'Temperature' THEN ParamValue END) Temperature,
    SUM(CASE WHEN ParamName = 'Humidity' THEN ParamValue END) Humidity
FROM yourTable
GROUP BY CityId

See SQL Fiddle with Demo

Upvotes: 1

Related Questions