CODEWITHSUNDEEP
mysql
simonbs
simonbs

Reputation: 8042

Select column as other column

I have the following table named attributes:

| identifier | attribute | value      |
|------------|-----------|------------|
| 23         | myKey     | myValue    |
| 24         | hisKey    | hisValue   |
| 25         | herKey    | herValue   |
| 23         | otherKey  | otherValue |

I would like to make SQL select query which selects the above rows, groups them by their identifier and uses the attribute as the key and the value as value for the key.

This means that the a table which looks like the above, should turn into the following PHP array:

Array
(
    [0] => Array
        (
            [identifier] => 23
            [myKey] => myValue
            [otherKey] => otherValue
        )

    [1] => Array
        (
            [identifier] => 24
            [hisKey] => hisValue
        )

    [2] => Array
        (
            [identifier] => 25
            [herKey] => herValue
        )

)

I have tried the following SQL query:

SELECT attributes.value as atttributes.attribute FROM attributes GROUP BY identifier

This will give the following error:

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.attribute FROM attributes GROUP BY identifier' at line 1'

Does anyone know of a way to do this?

Upvotes: 0

Views: 243

Answers (2)

colonelclick
colonelclick

Reputation: 2215

The answer is that you cannot do this with SQL in a reasonable fashion.

Upvotes: 1

Marcus Adams
Marcus Adams

Reputation: 53860

Your error is from a typo. Don't specify the table in an alias.

SELECT attributes.value as atttributes.attribute FROM attributes GROUP BY identifier

Here's the correction:

SELECT attributes.value as attribute FROM attributes GROUP BY identifier

However, it won't get you want you want. MySQL will always return results with the same number of columns in each row.

You'll need to implement this in code with a loop. Instead of GROUP BY, use an ORDER BY, and whenever the identifier changes, start another array.

Upvotes: 1

Related Questions