Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve row-to-column conversion operation in mysql. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Create table tx (

Id int primary key

C1 char (2)

C2 char (2)

Money int

Num int

);

Insert into tx values

(1,'A1','A1','B1','B1','81')

(2,'A _ 2','A _ 2','B _ 1,'7, 53)

(3,'A3','A3','B1','B1')

(4,'A4','A4','B1','B1,'2, 91)

(5,'A1','A1','B2','B2','A1','B2','A1')

(6,'A _ 2, B _ 2, C, 9, R, 66)

(7,'A3, B2, 8, 84)

(8,'A4, B2, 5, 55)

(9,'A1','A1','B3','B3','61)

(10,'A2, B3). B3.)

(11,'A3','B3','B3')

(12,'A4 recording, B3 recording, 6, 72)

(13,'A1 recording, B4 recording, 8, 33)

(14, 'A2pm, B4pm, 2p24)

(15,'A3, B4, B4, 6, 76)

(16,'A4, B4, B4, 9, 51)

(17,'A1','A1','B4','B4')

(18,'A _ 2','A _ 2','B _ 4,'5, 26)

(19,'A3','B4','B4')

(20, 'A4pm, B4pm 5pl 11)

/ * the first static column * /

Select ifnull (C1 recording total')

Sum (if (c2cycles, B1s)) AS B1money

Sum (if (c2cm / c2cm / B1mm / B1mm / B1mm) / AS B1num

Sum (if (c2pm / c2cm / B2mm)) AS B2money

Sum (if (c2cm / c2cm / B2mm / B2mm / B2mm /

Sum (if (c2pm / c2cm / B3mm)) AS B3money

Sum (if (c2cm / c2cm / B3mm / B3mm) / AS B3num)

Sum (if (c2pm / c2cm / B4mm)) AS B4money

Sum (if (c2cm / c2cm / B4mm / B4mm) / AS B4num

SUM (money) AS TOTAL

SUM (num) AS TOTAL

From tx

Group by c1 with rollup

/ * the second dynamic column * /

SET @ EE=''

SELECT @ EE:=CONCAT (

@ EE

'SUM (IF (C2 =\', C2Jing'\')

', money,0)) AS'

C2

'money,'

'SUM (IF (C2 =\', C2Jing'\')

', num,0)) AS'

C2

'num,') FROM (SELECT DISTINCT C2 FROM TX) A

SET @ QQ=CONCAT ('SELECT ifnull (C1,\' total\'),', LEFT (@ EE,LENGTH (@ EE)-1),', SUM (money) AS moneyTOTAL,SUM (num) AS numTOTAL FROM

TX GROUP BY C1 WITH ROLLUP')

PREPARE stmt2 FROM @ QQ

EXECUTE stmt2

On how to achieve row-column conversion operations in mysql to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.