mysql 列转字段,mysql将字段转换为列

I have the following tables

table 1 table 2

id q_id content id w_id q_id c_id ranking

---------------------- ------------------------------------------------

95 2046 1=E 123 22404 2046 100 1

96 2046 2=G 124 22404 2046 101 2

97 2046 3=N 125 22404 2046 102 2

98 2046 4=B 126 22404 2046 103 2

99 2046 5=V 127 22404 2046 104 3

100 2046 A1 128 22404 2046 105 3

101 2046 A2

102 2046 A3

103 2046 A4

104 2046 A5

105 2046 A6

I need to transfrom the table row to column

The original Result:

c_id content E G N B V

----------------------------------------------

100 A1 1 0 0 0 0

101 A2 0 1 0 0 0

102 A3 0 1 0 0 0

103 A4 0 1 0 0 0

104 A5 0 0 1 0 0

105 A6 0 0 1 0 0

The code of Result 1 :

(SELECT c.id, c.content, a.E, a.G, a.N, a.B, a.V FROM table_1 t

INNER JOIN

(SELECT t1.id,

Count(IF(t2.ranking=1,1,0)) AS E,

Count(IF(t2.ranking=2,1,0)) AS G,

Count(IF(t2.ranking=3,1,0)) AS N,

Count(IF(t2.ranking=4,1,0))AS B,

Count(IF(t2.ranking=5,1,0)) AS V

FROM table_1 t1, table_2 t2

WHERE t1.question_id = 2046 AND t2.question_id = 2046 AND t2.choice_id = t1.id

AND t2.ranking >= 0 AND t2.w_id IN (22404)

GROUP BY t1.id) a ON a.id = t1.id);

To New Result:

content A1 A2 A3 A4 A5 A6

-----------------------------------------------

1=E 1 0 0 0 0 0

2=G 0 1 1 1 0 0

3=N 0 0 0 0 1 1

4=B 0 0 0 0 0 0

5=V 0 0 0 0 0 0

I am using MySql and I can't use pivot. Also, I think I will not know the no. of "A" in table 1 which means it may up to "A30". Therefore it should be dynamic.....

Can anyone give me advice for Result 2?

@bluefeet I think I need to add two more condition in where caluse since the left join may joined large amount of data in table_1. So it can't show the result.

select c.content,

sum(case when t1.content = 'A1' then 1 else 0 end) A1,

sum(case when t1.content = 'A2' then 1 else 0 end) A2,

sum(case when t1.content = 'A3' then 1 else 0 end) A3,

sum(case when t1.content = 'A4' then 1 else 0 end) A4,

sum(case when t1.content = 'A5' then 1 else 0 end) A5

from table_1 c

left join table_2 t2

on left(c.content, 1) = t2.ranking

left join table_1 t1

on t2.c_id = t1.id

where locate('=', c.content) > 0 and c.id IN (95,96,97,98,99) and w_id = 22404

group by c.content;

The result would be like this

| CONTENT | A1 | A2 | A3 | A4 | A5 | A6 |

-----------------------------------------

| 1=E | 1 | 0 | 0 | 0 | 0 | 0 |

| 2=G | 0 | 1 | 1 | 1 | 0 | 0 |

| 3=N | 0 | 0 | 0 | 0 | 1 | 1 |

two rows are missing (4=B , 5=V).

How can I solve it?

解决方案

The following query should give you the result, however it makes a few assumptions:

The final content column will always have an equal sign = present. This is being used to locate the rows that are in the content.

The content column values with the = sign have the associated rank as the first character. This character is used to join the rows to the ranking in table_2

If you have a known number of values, then you can use the following:

select c.content,

sum(case when t1.content = 'A1' then 1 else 0 end) A1,

sum(case when t1.content = 'A2' then 1 else 0 end) A2,

sum(case when t1.content = 'A3' then 1 else 0 end) A3,

sum(case when t1.content = 'A4' then 1 else 0 end) A4,

sum(case when t1.content = 'A5' then 1 else 0 end) A5

from table_1 c

left join table_2 t2

on left(c.content, 1) = t2.ranking

left join table_1 t1

on t2.c_id = t1.id

where locate('=', c.content) > 0

group by c.content;

If you are going to have an unknown number of A values, then you can use a prepared statement to generate dynamic SQL:

SET @sql = NULL;

SELECT

GROUP_CONCAT(DISTINCT

CONCAT(

'sum(CASE WHEN t1.content = ''',

content,

''' THEN 1 else 0 END) AS `',

content, '`'

)

) INTO @sql

FROM Table_1

where locate('=', content)= 0;

SET @sql

= CONCAT('SELECT c.content, ', @sql, '

from table_1 c

left join table_2 t2

on left(c.content, 1) = t2.ranking

left join table_1 t1

on t2.c_id = t1.id

where locate(''='', c.content) > 0

group by c.content;');

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both give the result:

| CONTENT | A1 | A2 | A3 | A4 | A5 | A6 |

-----------------------------------------

| 1=E | 1 | 0 | 0 | 0 | 0 | 0 |

| 2=G | 0 | 1 | 1 | 1 | 0 | 0 |

| 3=N | 0 | 0 | 0 | 0 | 1 | 1 |

| 4=B | 0 | 0 | 0 | 0 | 0 | 0 |

| 5=V | 0 | 0 | 0 | 0 | 0 | 0 |