Werte zu Spalten-Namen

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
Ciatronical
Benutzer
Beiträge: 61
Registriert: Sa 21. Aug 2010, 13:10

Werte zu Spalten-Namen

Beitrag von Ciatronical » Mo 26. Aug 2019, 14:44

Hey All,

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

Code: Alles auswählen

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

akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Werte zu Spalten-Namen

Beitrag von akretschmer » Mo 26. Aug 2019, 15:17

geht:

Code: Alles auswählen

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.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

castorp
Erfahrener Benutzer
Beiträge: 1141
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Werte zu Spalten-Namen

Beitrag von castorp » Mo 26. Aug 2019, 15:52

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:

Code: Alles auswählen

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:

Code: Alles auswählen

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
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Ciatronical
Benutzer
Beiträge: 61
Registriert: Sa 21. Aug 2010, 13:10

Re: Werte zu Spalten-Namen

Beitrag von Ciatronical » Mo 26. Aug 2019, 17:57

Hey,

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

Code: Alles auswählen

 	$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:

Code: Alles auswählen

 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.

akretschmer
Erfahrener Benutzer
Beiträge: 7640
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Werte zu Spalten-Namen

Beitrag von akretschmer » Mo 26. Aug 2019, 18:08

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:

Code: Alles auswählen

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/plpg ... CUTING-DYN
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Ciatronical
Benutzer
Beiträge: 61
Registriert: Sa 21. Aug 2010, 13:10

Re: Werte zu Spalten-Namen

Beitrag von Ciatronical » Mo 26. Aug 2019, 18:16

Ok. Vielen Dank.

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

castorp
Erfahrener Benutzer
Beiträge: 1141
Registriert: Sa 27. Jun 2009, 22:24
Wohnort: Bayern

Re: Werte zu Spalten-Namen

Beitrag von castorp » Mo 26. Aug 2019, 18:34

Geht auch:

Code: Alles auswählen

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

Code: Alles auswählen

select jsonb_object_agg("key", val)
from test
where employee = 'emp1';
Online Beispiel: https://rextester.com/WSS2052
A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

Ciatronical
Benutzer
Beiträge: 61
Registriert: Sa 21. Aug 2010, 13:10

Re: Werte zu Spalten-Namen

Beitrag von Ciatronical » Mo 26. Aug 2019, 18:50

Super Danke!

Code: Alles auswählen

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.

pogomips
Erfahrener Benutzer
Beiträge: 105
Registriert: Fr 18. Nov 2016, 09:55

Re: Werte zu Spalten-Namen

Beitrag von pogomips » Mo 26. Aug 2019, 21:21

Ciatronical hat geschrieben:
Mo 26. Aug 2019, 18:50
Super Danke!

Code: Alles auswählen

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.
Vielleicht möchtest Du die Klammern anders setzen:

Code: Alles auswählen

select jsonb_object_agg("key", val)
from test
where  employee = 'emp1'  AND (key = 'key1' OR key = 'key2');
oder vielleicht geht auch das für Dich

Code: Alles auswählen

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

Antworten