How can i show dynamic row data into dynamic column

Question

Hi all i have one table which have two colums i want to show column 2 data in different column nothing is fix every thing is dynamic like column creation based on rows

table 1

id col1 col2
1   x   aa
2   x   bb
3   x   cc
4   y   ww
5   y   ee
6   z   hh
7   z   tt
8   z   uu
9   z   pp
10  z   oo

i want table1 data in this format

id val1 val2 val3 val4 val5 val6
1   x    aa   bb   cc   null null
2   y    ww   ee   null null null
3   z    hh   tt   uu   pp   oo

can any one please help me out how can i do this in mysql

search for 'pivot' queries, good luck with `everything is dynamic`. here are 3 at random http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot — Paul Maxwell, Aug 18 '14 at 05:21
just because there is no pivot feature in MySQL does not mean you cannot perform pivots, because yo can. using `group by` `case expressions` and `aggregate functions` - did you look at any of those URLs? — Paul Maxwell, Aug 19 '14 at 22:44
I am not getting any solution for this can any one please guide me about this query. its urgent — gaurav, Aug 20 '14 at 03:59

score 0 · Answer 1 · answered Aug 20 '14 at 04:27

this result:

| ID | VAL1 | VAL2 | VAL3 |   VAL4 |   VAL5 |   VAL6 |
|----|------|------|------|--------|--------|--------|
|  1 |    x |   aa |   bb |     cc | (null) | (null) |
|  4 |    y |   ww |   ee | (null) | (null) | (null) |
|  6 |    z |   hh |   tt |     uu |     pp |     oo |

from this query:

SELECT
      MIN(id)                                AS id
    , col1                                   AS val1
    , MAX(CASE WHEN colno = 1 THEN col2 END) AS val2
    , MAX(CASE WHEN colno = 2 THEN col2 END) AS val3
    , MAX(CASE WHEN colno = 3 THEN col2 END) AS val4
    , MAX(CASE WHEN colno = 4 THEN col2 END) AS val5
    , MAX(CASE WHEN colno = 5 THEN col2 END) AS val6
FROM (
      SELECT
               @row_num :=IF(@prev_value = t.col1, @row_num + 1,1) AS colno
             , t.id
             , t.col1
             , t.col2
             , @prev_value := t.col1 as pv
      FROM Table1 t
      CROSS JOIN(SELECT @row_num := 1 x, @prev_value :='' y) vars
      ORDER BY t.col1, t.id ASC
      ) sq
GROUP BY
      col1
ORDER BY
      col1
;

But you should note that sites such as this are not places where we just cough up finished work for you for nothing. You are supposed to demonstrate what you have tried at the very least. Claiming urgency isn't helpful, those who provide answers also have work to do and this is entirely voluntary.

Please prepare your next question with some evidence of research and an attempted query.

How can i show dynamic row data into dynamic column

1 Answers1