MySQL数据透视表 [英] MySQL Pivot Table

查看:83
本文介绍了MySQL数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在过去的几天中,我一直在寻找解决此问题的方法.虽然我没有找到答案,但该网站一直出现在我的搜索结果中,因此我想尝试一下.顺便说一句,很棒的帖子格式选项.

Over the past few days, I have been trying to find an answer to this problem. While I have not found an answer, this site keep appearing in my search results so I thought I would give it a try. Awesome formatting options for posts BTW.

我有一个类似的表:

user_id | form_id | question_id | data_label | data_value  
1         1         1             firstName    Joe  
1         1         2             lastName     Smith  
1         1         3             phone        5554443333  
2         1         1             firstName    Sally  
2         1         2             lastName     Jones  
2         1         3             phone        3334445555  

我想变成的

:

that I want to turn into:

user_id | firstName | lastName | phone  
1         Joe         Smith      5554443333  
2         Sally       Jones      3334445555  

我可以找到有关如何执行此操作的示例.我不太了解它们,但它们在那里.当此单个表保存从各种形式输入的数据以及任意数量的字段时,我的独特问题开始生效.因此,我可能有一个带有以下内容的表:

I can find example of how to do this. I don't understand them very well but they are out there. My unique problem comes into effect when this single table holds data entered from a variety of forms with any possible number of fields. So, I might have a table with:

user_id | form_id | question_id | data_label | data_value  
1         1         1             firstName    Joe  
1         1         2             lastName     Smith  
1         1         3             phone        5554443333  
2         1         1             firstName    Sally  
2         1         2             lastName     Jones  
2         1         3             phone        3334445555  
3         2         1             fav_color    red  
3         2         2             fav_animal   eagle  
4         2         1             fav_color    blue  
4         2         2             fav_animal   dog  

然后我将form_id作为参数传递,从该表单中按用户分组收集所有记录(它们都应具有带有不同值的相同标签),然后将该数据显示为:

I will then pass in the form_id as a parameter, gather all records from that form grouped by users (they should all have the same labels with different values), and then display that data as:

...当form_id = 1时,报告如下:

...when form_id = 1 the report looks like:

user_id | firstName | lastName | phone  
1         Joe         Smith      5554443333  
2         Sally       Jones      3334445555  

...当form_id = 2时,报告如下:

...when form_id = 2 the report looks like:

user_id | fav_color | fav_animal  
3         red         eagle  
4         blue        dog  

我是高级SQL编程和过程的新手,无法自行解决如何做到这一点.我需要查询能够处理任何数量/类型的字段,而不必在每种表单的查询中输入确切的可能字段名称.请注意,紧接在上面的第一个查询具有四个字段,而第二个查询具有3个字段,因此解决方案需要以这种方式灵活.

I am new to advanced SQL programming and procedures and am not able to figure out how to do this on my own. I need the query to be able to handle any number/type of fields without having to enter the exact possible field names into the query for each form. Notice the first query immediately above has four fields while the second has 3. So the solution needs to be flexible in this manner.

SQL也可以从编程语言中生成,因此,如果有帮助的话,该解决方案还有更多选择.

The SQL can also be generated from a programming language so there are more options with that solution if that helps.

如果您没有足够的信息,请通知我.

Please inform me if you do not have enough information.

推荐答案

您可以尝试(未经测试)类似

You could try (untested) something like

select form_id, 
max(case data_label when 'lastName' then data_value else null end) as lastname, 
max(case data_label when 'firstName' then data_value else null end) as firstname,
max(case data_label when 'phone' then data_value else null end) as phone
from mytable 
group by form_id

这篇关于MySQL数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆