How can I get a "traditional" single row result from a key-value oriented table layout

I have a table which was designed as a key-value table, like

ID | key | value
1  | abc | value 1
2  | def | value 2
3  | geh | value 3

which has various benefits for what we are doing with the data. Only drawback is, I can't sort easily on such a key-value table. What 开发者_高级运维would be an intelligent/usual way to get a result-set with all the keys/values "flattened out" in a traditional way, with the keys appearing as fields:

abc     | def     | geh
value 1 | value 2 | value 3

You can only do this with a stored procedure and you wouldn't win much performancewise.

To bring out the most of it, you can create an index on the key-kvalue tabe with:

CREATE UNIQUE INDEX myindex ON keyvaluetable(key)

I assumed you have UNIQUE values on the key field. If not, you can of course delete that part.

You are overnormalizing, but your example is incomplete. If you are looking at your original example and try to add one more row, you will see that it is not clear from your table to which row a value belongs. You need to add an item column:

root@localhost [kris]> create table overnormal ( id serial, k varchar(20) not null, v varchar(20) not null);
Query OK, 0 rows affected (0.96 sec)

root@localhost [kris]> insert into overnormal values ( 1, 'abc', 'value 1'), (2, 'def', 'value 2'), (3, 'geh', 'value 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [kris]> select * from overnormal;
+----+-----+---------+
| id | k   | v       |
+----+-----+---------+
|  1 | abc | value 1 |
|  2 | def | value 2 |
|  3 | geh | value 3 |
+----+-----+---------+
3 rows in set (0.00 sec)

Let's add the item column:

root@localhost [kris]> alter table overnormal add column item integer unsigned not null;
Query OK, 3 rows affected (0.48 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [kris]> update overnormal set item = 1;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

root@localhost [kris]> insert into overnormal values (4, 'abc', 'item 1/1', 2), (5, 'def', 'item 1/2', 2), (6, 'geh', 'item 1/3', 2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost [kris]> select * from overnormal;
+----+-----+----------+------+
| id | k   | v        | item |
+----+-----+----------+------+
|  1 | abc | value 1  |    1 |
|  2 | def | value 2  |    1 |
|  3 | geh | value 3  |    1 |
|  4 | abc | item 1/1 |    2 |
|  5 | def | item 1/2 |    2 |
|  6 | geh | item 1/3 |    2 |
+----+-----+----------+------+
6 rows in set (0.00 sec)

You can transform this into a traditional table using the join of death:

root@localhost [kris]> select t1.item, t1.v as abc, t2.v as def, t3.v as geh 
    from overnormal as t1 
    join overnormal as t2 
        on t1.item = t2.item 
       and t1.k = 'abc' 
       and t2.k = 'def' 
    join overnormal as t3 
        on t1.item = t3.item 
       and t3.k = 'geh';
+------+----------+----------+----------+
| item | abc      | def      | geh      |
+------+----------+----------+----------+
|    1 | value 1  | value 2  | value 3  |
|    2 | item 1/1 | item 1/2 | item 1/3 |
+------+----------+----------+----------+
2 rows in set (0.00 sec)

You can sort this the usual way by assing an ORDER BY clause.

This query is not that bad, but will rapidly degrade in efficiency as your table gets wider, even with indexes, as the optimizer will get confused at 9 to 10 tables in a single join.

You are better off with a data model that closer to 3rd normal form and less close to DKNF, and yes, that is possible and less a problem than you think. You cannot handle completely arbitrary data types anyway without code making assumptions about the k-values that are legal and required in your application anyway (or, if you are not making such assumptions, you might as well serialize your in-app structure and store a blob).