CODEWITHSUNDEEP
mysqlsql
Jake
Jake

Reputation: 145

Is it possible to query result column name as row value?

The table in database is like this format

 id    Name         Month   Spending

1001    Murphy      JAN       500
1001    Murphy      FEB       750
1001    Murphy      MAR       300
1002    Patterson   JAN       155
1002    Patterson   FEB       85
1002    Patterson   MAR       60
1003    Firrelli    JAN       125
1003    Firrelli    FEB       100
1003    Firrelli    MAR       250

for plot data in graph i need the query result like this..

id    name          JAN    FEB    MAR

1001   Murphy       500    750    300
1002   Patterson    155    85     60
1003   Firrelli     125    100    250

is it possible to get query result like this?

No need to create a table, just query result only.

Upvotes: 2

Views: 62

Answers (2)

wvdz
wvdz

Reputation: 16641

If the number of months is static, this can be done in a variety of ways, one being like this:

SELECT id, Name, SUM(JAN) AS JAN, SUM(FEB) AS FEB, SUM(MAR) AS MAR
FROM
  (SELECT id, name, Spending AS JAN, 0 AS FEB, 0 AS MAR
  FROM t1
  WHERE Month = 'JAN'
  UNION ALL
  SELECT id, name, 0, Spending, 0
  FROM t1
  WHERE Month = 'FEB'
  UNION ALL
  SELECT id, name, 0, 0, Spending
  FROM t1
  WHERE Month = 'MAR')
GROUP BY id, Name

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Use Conditional Aggregate to transpose the rows to columns. Try this.

select id,Name,
max(case when Month ='Jan' then Spending end) Jan,
max(case when Month ='Feb' then Spending end) Feb,
max(case when Month ='Mar' then Spending end) Mar
from yourtable
group by id,Name

Upvotes: 3

Related Questions