Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

pivot table - Row to column transformation in MySQL

I have this result set in MySQL :

ID          Type Email          Degignation  
1000000000  202 [email protected] Entrepreneur
1000000000  234 [email protected] Engineering,Development
1000000000  239 [email protected] CTO

I have many such tuples not only three . I want Type to be column and last column to become row value for them . Like below

ID                202         234                     239        Email 
1000000000   Entrepreneur  Engineering,Development    CTO       [email protected]
question from:https://stackoverflow.com/questions/65947863/add-custom-column-from-existing-row

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This is called a pivot table. It's kind of awkward to produce:

SELECT ID, 
 MAX(CASE Type WHEN 202 THEN Degignation END) AS `202`
 MAX(CASE Type WHEN 234 THEN Degignation END) AS `234`
 MAX(CASE Type WHEN 239 THEN Degignation END) AS `239`
 Email
FROM mytable
GROUP BY ID, Email

Note that you must know all the distinct Type values before you write the query. SQL doesn't allow a result set to add more columns dynamically as it discovers data values in the table. Columns must be fixed at query prepare time.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...