CODEWITHSUNDEEP
mysqlsqlpivotunpivot
natlines
natlines

Reputation: 7716

SQL Turn Column headers into Row

I'm fairly new to SQL so please forgive this.

I have a table like this:

+---------+-------+-----+-----+-----+
| Vehicle |  Kms  |  6  | 12  | 18  |
+---------+-------+-----------+-----+
|  Car    | 30000 | 53% | 50% | 47% |
|  Car    | 40000 | 50% | 47% | 44% |
|  Bus    | 50000 | 47% | 44% | 42% |
+---------+-------+-----------+-----+

The number in the header row are actually years, and I want to aggregate them into an 'Age' column like so.

+---------+-------+-----+------------+
| Vehicle |  Kms  | Age | Percentage |
------------------------+------------+
|  Car    | 30000 | 6   |   53%      |
|  Car    | 30000 | 12  |   50%      |
|  Car    | 30000 | 12  |   47%      |
|  Car    | 40000 | 6   |   50%      |
+---------+-------+-----+------------+

I've looked into using PIVOT but I don't want to aggregate the Kms column. Any idea how I can achieve this?

Upvotes: 1

Views: 190

Answers (2)

cew
cew

Reputation: 144

The method that I would recommend is a combination of join and case where the you join to your pivot table and then use case to select the values you want.

Different databases may have other more efficient non ANSI solutions, so if it is good to know what database you are using and whether or not you want to stick to pure ANSI for portability or tune optimally for your platform.

Two choices for the join:

  1. Provide the join table inline:

    SELECT
  `Vehicle`,
  `Age`,
  CASE when `Age` = 6 then `6`
     when `Age` = 12 then `12`
     when `Age` = 18 then `18`
  END as `Percent`
FROM 
  t1, (select 6 as `Age` union all
       select 12 as `Age` union all
       select 18 as `Age`
       ) as age_pivot;

    SQL fiddle here

  2. Construct the join table first and then join to it:

    SELECT
  `Vehicle`,
  `Age`,
  CASE when `Age` = 6 then `6`
       when `Age` = 12 then `12`
       when `Age` = 18 then `18`
  END as `Percent`
FROM t1, age_pivot;

    SQL fiddle here

The second becomes handy to keep your query reasonably consise as the size of your pivot table grows.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One method is to use union all

select vehicle, kms, '6' as age, `6` as percentage from table t
union all
select vehicle, kms, '12' as age, `12` as percentage from table t
union all
select vehicle, kms, '18' as age, `18` as percentage from table t;

If you have large data, there are other methods that do not require three table scans, but this works for moderate amounts of data.

If you only want this for cars, then the most efficient ways is to use a where clause for each subquery.

Upvotes: 0

Related Questions