Werte zu Spalten-Namen

Anwendungen & Erweiterungen SQL
1

Hey All,

ich habe eine Tabelle mit Config-Werten (id key value user ).
Etwa so:

DROP TABLE  IF EXISTS test;
CREATE TABLE test( id serial, key text, val text, employee text );
INSERT INTO test( key, val, employee ) VALUES ( 'key1', 'value1', 'emp1' );
INSERT INTO test( key, val, employee ) VALUES ( 'key2', 'value2', 'emp1' );
INSERT INTO test( key, val, employee ) VALUES ( 'key3', 'value3', 'emp1' );
INSERT INTO test( key, val, employee ) VALUES ( 'key4', 'value4', 'emp1' );
SELECT key, val FROM test WHERE employee = 'emp1';

Ergibt erwartungsgemäß:
key | val
------±-------
key1 | value1
key2 | value2
key3 | value3
key4 | value4

Nun möchte ich die Keys zu Spaltennamen machen.
So:
key1 | key2 | key3 | key4
--------±--------±--------±-----
value1 |value2 | value3 | value4

Geht das?? Wenn ja wie??

Viele Grüße Ronny

2

geht:

test=*# select * from test;
 id | key  |  val   | employee 
----+------+--------+----------
  1 | key1 | value1 | emp1
  2 | key2 | value2 | emp1
  3 | key3 | value3 | emp1
  4 | key4 | value4 | emp1
(4 rows)

test=*# select array_to_string(array_agg(case when key = 'key1' then val else null end), ''::text) as key1, array_to_string(array_agg(case when key = 'key2' then val else null end), ''::text) as key2, array_to_string(array_agg(case when key = 'key3' then val else null end), ''::text) as key3, array_to_string(array_agg(case when key = 'key4' then val else null end), ''::text) as key4 from test;
  key1  |  key2  |  key3  |  key4  
--------+--------+--------+--------
 value1 | value2 | value3 | value4
(1 row)

test=*#

Das ist aber statisch, also, wenn neue val-Werte dazukommen, mußt Du das anpassen. Wenn das nicht reicht: dynamisch das SQL generieren und ausführen.

3

Das läuft unter dem Begriff “pivot” oder “crosstab” - etwas was in SQL grundsätzlich nicht so einfach ist, das eine der Limitierungen der Sprache ist, dass die Anzahl (und Datentypen) einer Abfrage vor dem Ausführen eine Abfrage fest stehen muss.

Deswegen muss man diese Art von Abfragen immer anpassen wenn zusätzliche Spalten dazukommen sollen. Aus diesem Grund ist es im Regelfall wesentlich einfacher und flexibler dies in der Anwendung zu machen, welche die Daten anzeigt. Nicht schon beim Lesen der Daten.

Normalerweise verwendet man dafür bedingte Aggregation:

select max(val) filter (where "key" = 'key1') as key1,
       max(val) filter (where "key" = 'key2') as key2,
       max(val) filter (where "key" = 'key3') as key3,
       max(val) filter (where "key" = 'key4') as key4
from test
where employee = 'emp1';

Da für jeden “key” (hoffentlich) immer nur ein Wert vorhanden sein kann, wird dieser mittels max() und filter() “rausgepickt”.

Das kann man dann auch für jeden Datensatz der Tabelle machen, wenn man ein GROUP BY mit dazu nimmt:

select employee, 
       max(val) filter (where "key" = 'key1') as key1,
       max(val) filter (where "key" = 'key2') as key2,
       max(val) filter (where "key" = 'key3') as key3,
       max(val) filter (where "key" = 'key4') as key4
from test
group by employee;

Online Beispiel: https://rextester.com/AZFG89750

4

Hey,

danke für die Antworten.
Also bisher hatte ich es so:

 	$sql = "SELECT key, val FROM mydefaults WHERE employee = 12  AND key = 'asterisk_passwd' OR key = 'ip_asterisk' OR key = 'user_external_context' OR key ='user_internal_phone' OR key = 'external_contexts' OR key = 'internal_phones'";
        $result = $GLOBALS['dbh']->getAll( $sql );
        $resultArray = array();
        foreach( $result as $value ) $resultArray[$value['key']] = $value['val'];

gemacht.

Das Ergebnis sieht dann so aus:

 Array
(
    [user_external_context] => value_x
    [external_contexts] => value_y
    [internal_phones] => value_z
    [ip_asterisk] => 127.0.0.1
    [asterisk_passwd] => mypasswd
    [user_internal_phone] => value_fon
)

Ich wollte die Logik in SQL implementieren.
@akretschmer

Wie kann ich das dynamisch in SQL realisieren?
Ich möchte in der Abfrage lediglich den employee und die keys angeben und mit getOne die Daten holen.
Wenn der entsprechende key nicht vorhanden ist wird null oder ein leerer String zurückgegeben.

Etwa so:
“wähle alles ( mache aus den Zeilen des Employees 12 eine Tabelle mit den Spaltennamen der angegebenen Keys (key1, key2, key3) und liefere deren Values) ;”

Die Abfrage gibt lediglich eine Zeile zurück.

5

Wie @castorp schon sagte: es ist einfacher, sich aus PG nur ein Objekt mit allen Daten geben zu lassen, und in der Applikation damit zu hantieren. Zum Beispiel JSON:

test=*# select * from test;
 id | key  |  val   | employee 
----+------+--------+----------
  1 | key1 | value1 | emp1
  2 | key2 | value2 | emp1
  3 | key3 | value3 | emp1
  4 | key4 | value4 | emp1
(4 rows)

test=*# select to_json(r) from (select json_agg(t.key) as key, json_agg(t.val) as val from test t) r;
                                         to_json                                         
-----------------------------------------------------------------------------------------
 {"key":["key1", "key2", "key3", "key4"],"val":["value1", "value2", "value3", "value4"]}
(1 row)

Dynamische Abfragen erzeugen & ausführen: https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

6

Ok. Vielen Dank.

Gehen vielleicht aus key:value Paare??
{“key1”:“value1”,“key2”:“value2”}

7

Geht auch:

select employee, jsonb_object_agg("key", val)
from test
group by employee;

oder

select jsonb_object_agg("key", val)
from test
where employee = 'emp1';

Online Beispiel: https://rextester.com/WSS2052

8

Super Danke!

select jsonb_object_agg("key", val)
from test
where ( employee = 'emp1' ) AND key = 'key1' OR key = 'key2';

Holt mir auch nur genau die Keys die ich benötige.