CODEWITHSUNDEEP
mysqlsqlpivot
Lupus
Lupus

Reputation: 1518

I need a way to use column values as column names in MySQL

I'm trying to create a nested structure;

id | degerAdi    | deger
_____________________________
1  | asd         | 1
2  | asd         | 2
3  | asd         | 345
4  | rty         | 6765
5  | rty         | ljkl
6  | hhh         | 567
7  | hjh         | 5674
8  | ffgu        | 567
9  | qwe         | 345345
10 | qwe         | fghfghfh
11 | qwe         | ghghjghjgj

I need a way to get result like the values of 'degerAdi' colmn as colmn names and associated values of 'deger' colmn as values (rows) of these colms.

(Sorry for my poor english language skills.) I'need result like this;

asd    | rty   | hhh  | hjh  | ffgu | qwe
-----------------------------------------------------
1      | 6765  | 567  | 5674 | 567  | 345345
2      | 6765  | NULL | NULL | NULL | fghfghfh
345    | NULL  | NULL | NULL | NULL | ghghjghjgj

Upvotes: 0

Views: 173

Answers (1)

Taryn
Taryn

Reputation: 247810

You are trying to PIVOT the data but MySQL does not have a PIVOT function. Also to make this easier, you will want to partition the data based on the degerAdi value to apply a rownumber. If you have a known number of columns, then you can use:

select rn,
  max(case when DEGERADI = 'asd' then DEGER end) asd,
  max(case when DEGERADI = 'rty' then DEGER end) rty,
  max(case when DEGERADI = 'hhh' then DEGER end) hhh,
  max(case when DEGERADI = 'hjh' then DEGER end) hjh,
  max(case when DEGERADI = 'ffgu' then DEGER end) ffgu,
  max(case when DEGERADI = 'qwe' then DEGER end) qwe
from
(
  select id, degerAdi, deger,
   @num := if(@degerAdi = `degerAdi`, @num + 1, 1) as rn,
   @degerAdi := `degerAdi` as dummy
  from table1
) x
group by rn;

See SQL Fiddle With Demo

If you have an unknown number of columns then you will want to use prepared statements:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when degerAdi = ''',
      degerAdi,
      ''' then deger end) AS ',
      degerAdi
    )
  ) INTO @sql
FROM Table1;

SET @sql 
  = CONCAT('SELECT rn, ', @sql, ' 
           from
           (
             select id, degerAdi, deger,
              @num := if(@degerAdi = `degerAdi`, @num + 1, 1) as rn,
              @degerAdi := `degerAdi` as dummy
             from table1
           ) x
           group by rn');

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

See SQL Fiddle with demo

Upvotes: 2

Related Questions