将列值显示为表头，而不是原来的表头

Question

我在mySQL里有一个数据库，假设是“存储”，里面有一个叫做"Storage_Det“的表。下面是“Storage_Det”的内容：

valueid | formid |  submissionid |  fieldname  |  fieldvalue
--------+--------+---------------+-------------+-------------
1       | 1      |  1            |  name       |  Alex
2       | 1      |  1            |  position   |  Manager
3       | 1      |  1            |  room       |  3-10
4       | 1      |  2            |  name       |  Ben
5       | 1      |  2            |  position   |  Accountant
6       | 1      |  2            |  room       |  2-05
7       | 1      |  3            |  name       |  Denny
8       | 1      |  3            |  position   |  Marketing
9       | 1      |  3            |  room       |  1-03

用php显示它对我来说没有问题。我的问题是:我想为我的新表视图显示"name“、"position”和"room“，这样它就会像这样显示：

submissionid | name   | position   | room |
-------------+--------+------------+------+
1            | Alex   | Manager    | 3-10 |
2            | Ben    | Accountant | 2-05 |
3            | Denny  | Marketing  | 1-03 |

因为我是新手，所以我需要你的帮助。请让我知道。谢谢。

好吧，我想我必须在之前添加一些细节，以使我的问题足够清楚。

“2号桌”不是真正的桌。它是从存储在表1中的数据生成的。正如您在“表1”数据中看到的那样，“name”、“position”和“room”将是“表2”中的“表头”。我希望这已经足够清楚了。

Answer 1

如果您希望查询按照您请求的方式显示数据，您可以使用以下命令：

SELECT DISTINCT sd.SubmissionId
   , sdName.FieldValue AS Name
   , sdPosition.FieldValue AS Position
   , sdRoom.FieldValue AS Room
FROM Storage_Det AS sd
LEFT JOIN Storage_Det AS sdName ON sd.SubmissionId = sdName.SubmissionId
   AND sdName.FieldName = 'Name'
LEFT JOIN Storage_Det AS sdPosition ON sd.SubmissionId = sdPosition.SubmissionId
   AND sdPosition.FieldName = 'Position'
LEFT JOIN Storage_Det AS sdRoom ON sd.SubmissionId = sdRoom.SubmissionId
   AND sdRoom.FieldName = 'Room'

Answer 2

现在我更好地理解了你的问题，这里有一种方法：

CREATE TEMPORARY TABLE  `table2` (
`submissionid` INT NOT NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
`position` VARCHAR( 50 ) NULL ,
`room` VARCHAR( 50 ) NULL
) ENGINE = MYISAM ;

ALTER TABLE  `table2` 
ADD UNIQUE (
`submissionid` ,
`name` ,
`position` ,
`room`
);

insert table2 (submissionid, name) select submissionid, fieldvalue from table1 where fieldname='name'; 
update table2 set position = (select fieldvalue from table1 where fieldname='position' and table1.submissionid = table2.submissionid); 
update  table2 set room = (select fieldvalue from table1 where fieldname='room' and table1.submissionid = table2.submissionid);   


select * from table2;

Answer 3

首先，像这样对某个submissionid的所有数据进行分组(假设使用非常基本的mysql API)：

$sIdGroup = array();
while ( $row = mysql_fetch_assoc( $result ) )
{
    if ( !isset( $sIdGroup[ $row[ "submissionid" ] ] ) )
    {
        $sIdGroup[ $row[ "submissionid" ] ] = array();
    }

    $sIdGroup[ $row[ "submissionid" ] ][ $row[ "fieldname" ] ] = $row[ "fieldvalue" ];  // Save the value for the current field
}

现在，首先显示列名：

echo( '<table><tr><td>submissionid</td>' );

// Get the first row in $sIdGroup (i'm assuming 1 is not always the first index)

reset( $sIdGroup );
$firstSubmissionId = key( $sIdGroup );

foreach ( $sIdGroup[ $firstSubmissionId ] as $key => $dummy )
{
    echo( "<td>$key</td>" );
}

现在来看数据：

echo( '</tr>' );

foreach ( $sIdGroup as $submissionId => $data )
{
    echo( "<tr><td>$submissionId</td>" );
    foreach ( $data as $key => $value )
    {
        echo( "<td>$value</td>" );
    }
    echo( '</tr>' );
}

echo( '</table>' );