CODEWITHSUNDEEP
sqlsql-server
Bora
Bora

Reputation: 127

How to rotate a table's column items to columns

I have a table and it needs to change

Key                  Value
CapacityColdBTU      2000
PowerLevelCold       B
Inverter/Standard    Standard
CapacityColdBTU      1500
PowerLevelCold       B
Inverter/Standard    Inverter

The table should look like below before I use it. I cannot use the table in searching with more than 1 criteria. How do I change it? 

CapacityColdBTU   PowerLevelCold    Inverter/Standard
2000              B                 Inverter

Thank you

Upvotes: 0

Views: 92

Answers (1)

Taryn
Taryn

Reputation: 247710

You can use PIVOT:

select *
from
(
  select [key], value
  from yourtable
) x
pivot
(
  max(value)
  for [key] in ([CapacityColdBTU], [PowerLevelCold], [Inverter/Standard])
) p

See SQL Fiddle with Demo

Or you can use a CASE statement with aggregate, note this version generates the result you have listed with the Inverter value in the [Inverter/Standard] field:

select 
  max(case when [key] = 'CapacityColdBTU' 
        then value end) as CapacityColdBTU,
  max(case when [key] = 'PowerLevelCold' 
        then value end) as PowerLevelCold,
  min(case when [key] = 'Inverter/Standard' 
        then value end) as [Inverter/Standard]
from yourtable

See SQL Fiddle with Demo

If you have an id or some field that identifies each of these fields with a main record, then you can display the values for each id.

Upvotes: 3

Related Questions