0

Possible Duplicate:
mysql converting multiple rows into columns in a single row

i have a mysql table like this:

id |  p |  c  |  v
1     p1   10    1
2     p1   20    2
3     p1   30    3
4     p2   40    1
5     p2   50    2
6     p2   60    3

now i need to run a sql and get result like this:

p  |  as_c1 | as_c2 | as_c3
p1     10       20      30
p2     40       50      60

i used this query but it's not enough:

select 
p, 
c as as_c1,
c as as_c2,
c as as_c3
from test_tbl group by p, c

i searched every where, is this possible? i just need some guide.

Community
  • 1
  • 1
Vahid
  • 382
  • 1
  • 6
  • 19

2 Answers2

2

This is basically a PIVOT that you are trying to perform. Unfortunately, MySQL does not have a PIVOT function. There are two ways to do this static or dynamic. If you know the values that you want to transform into columns, then you can use a static version but if the values are unknown then you can use a prepared statement to generate this dynamically:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when v = ''',
      v,
      ''' then c end) AS as_c',
      v
    )
  ) INTO @sql
FROM table1;

SET @sql = CONCAT('SELECT p, ', @sql, ' 
                    FROM table1 
                    GROUP BY p');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

The static version would be similar to this:

select p,
  sum(case when v=1 then c end) as_c1,
  sum(case when v=2 then c end) as_c2,
  sum(case when v=3 then c end) as_c3
from table1
group by p

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • why i get #1243 - Unknown prepared statement handler (stmt) given to EXECUTE when run dynamic way in localhost phpmyadmin? someone said this here: http://stackoverflow.com/questions/12270953/combine-multiple-rows-into-one-mysql but i can't understand what he did. – Vahid Oct 18 '12 at 07:45
0
SELECT p,
  ( CASE WHEN v = 1 THEN c ELSE NULL END ) AS as_c1,
  ( CASE WHEN v = 2 THEN c ELSE NULL END ) AS as_c2,
  ( CASE WHEN v = 3 THEN c ELSE NULL END ) AS as_c3
FROM `test_tbl`
GROUP BY p;

I think that ought to do it.

hjpotter92
  • 78,589
  • 36
  • 144
  • 183