Anfänger sucht Hilfe beim Erzeugen einer Kreuztabelle

[georg6170]

Hallo,

ich habe folgende Tabelle (user_profiles):

user_id | profile_key    | profile_value
----------------------------------------------
1      | profile.website | "www.beispiel.de"
1      | profile.ort    | "New York"

und möchte diese wie folgt transformieren:

user_id | profile.ort | profile.website
----------------------------------------
1      | New York    | www.beispiel.de

Dazu habe ich Folgendes versucht:

Select user_id,
case profile_key when "profile.website" then profile_value end as "Website",
case profile_key when "profile.ort" then profile_value end as "Ort",
from frXXX_user_profiles
group by user_id

Leider erhalte ich dann nur Null-Werte bei Website und Ort. Ich habe das Gefühl das nur die jeweils erste Zeile abgefragt wird.

Was mache ich falsch?

Danke für Eure Hilfe.

 

[akretschmer]

Hallo,

ich habe folgende Tabelle (user_profiles):

user_id | profile_key    | profile_value
----------------------------------------------
1      | profile.website | "www.beispiel.de"
1      | profile.ort    | "New York"

und möchte diese wie folgt transformieren:

user_id | profile.ort | profile.website
----------------------------------------
1      | New York    | www.beispiel.de

Dazu habe ich Folgendes versucht:

Select user_id,
case profile_key when "profile.website" then profile_value end as "Website",
case profile_key when "profile.ort" then profile_value end as "Ort",
from frXXX_user_profiles
group by user_id

Leider erhalte ich dann nur Null-Werte bei Website und Ort. Ich habe das Gefühl das nur die jeweils erste Zeile abgefragt wird.

Was mache ich falsch?

Danke für Eure Hilfe.

Ich hab es mal in PostgreSQL versucht:

test=*# select * from georg;
 user_id |   key   |   value
---------+---------+------------
       1 | website | www.bla.de
       1 | ort     | dresden
(2 rows)

Time: 0,179 ms
test=*# select user_id, case when key = 'website' then value else null end as website, case when key='ort' then value else null end as ort from georg; user_id |  website   |   ort
---------+------------+---------
       1 | www.bla.de |
       1 |            | dresden
(2 rows)

Time: 0,230 ms
test=*# select user_id, array_agg(case when key = 'website' then value else null end) as website, array_agg(case when key='ort' then value else null end) as ort from georg group by user_id;
 user_id |      website      |      ort
---------+-------------------+----------------
       1 | {www.bla.de,NULL} | {NULL,dresden}
(1 row)

Time: 0,322 ms
test=*# select user_id, array_to_string(array_agg(case when key = 'website' then value else null end),'') as website, array_to_string(array_agg(case when key='ort' then value else null end),'') as ort from georg group by user_id;
 user_id |  website   |   ort
---------+------------+---------
       1 | www.bla.de | dresden
(1 row)

Zuerst Deine pure Tabelle, dann etwa so wie Dein Ansatz. Da habe aber dann 2 Zeilen, diese fasse ich mit einer Aggregation zu einem Array zusammen. Da das nicht so schön ausschaut, wandle ich das Array dann wieder zu einem String.

Vielleicht kannst das ja so verwenden, IIRC hatten wir das hier auch schon so 2-3 mal.

 

[akretschmer]

Ich hab es mal in PostgreSQL versucht:

Schau mal in den anderen Thread von heute: da wird group_concat verwendet. Vielleicht hilft Dir das.

 

[georg6170]

Hallo Akretschmer,

danke für Deine Hilfe.

Leider kennt mysql die Funktion "array_agg" nicht. Ich werde es aber mal mit group_concat probieren.

Ich habe nochmal hier im Forum nachgesehen und gegoogled. Du hast das Thema schon mal in einem anderen Forum behandelt.

In diesem bin ich mit "Kreuztabelle, Pivot, Zeile-->Spalten etc. nicht weiter gekommen.

 

[georg6170]

Ich habe jetzt mal group_concat ausprobiert.
Ja es geht. Aber wirklich begquem ist das vor allem für Anfänger nicht. Jetzt muss ich irgendwie eine Schleife basteln, mit der ich den String zerlegen kann.

Eine einfache Lösung mit Spalten in mysql wäre mir lieber gewesen. Aber besser als nichts ist es ...

 

[akretschmer]

Ich habe jetzt mal group_concat ausprobiert.
Ja es geht. Aber wirklich begquem ist das vor allem für Anfänger nicht. Jetzt muss ich irgendwie eine Schleife basteln, mit der ich den String zerlegen kann.

Eine einfache Lösung mit Spalten in mysql wäre mir lieber gewesen. Aber besser als nichts ist es ...

Welchen String zerlegen?

Ach ja, ich hätte noch eine kürzere Lösung, basierend auf einem Datentyp aber, den es so auch in PostgreSQL erst mal nicht gibt, aber als Extension verfügbar ist: hstore.

test=*# create table georg(id int, values hstore);
CREATE TABLE
Time: 4,498 ms
test=*# insert into georg values (1, 'website=>www.bla.de'::hstore);
INSERT 0 1
Time: 0,384 ms
test=*# insert into georg values (1, 'ort=>dresden'::hstore);
INSERT 0 1
Time: 0,154 ms
test=*# select id, max(values -> 'website') as website, max(values -> 'ort') as ort from georg group by id;
 id |  website   |   ort
----+------------+---------
  1 | www.bla.de | dresden
(1 row)

Oder noch kürzer:

test=*# create table georg(id int, values hstore);
CREATE TABLE
Time: 63,761 ms
test=*# insert into georg values (1, 'website=>www.bla.de, ort=>dresden'::hstore);
INSERT 0 1
Time: 0,381 ms
test=*# select id, values -> 'website' as website, values -> 'ort' as ort from georg;
 id |  website   |   ort
----+------------+---------
  1 | www.bla.de | dresden
(1 row)

HSTORE ist ein Key->Value - Speicher, damit kann man noch einiges mehr machen.
Damit wird es noch einfache, wie man sieht - geht aber ganz sicher so nicht in MySQL.

 

[georg6170]

Wenn ich den Befehl group_concat nehme, erhalte ich:

id | Tag
---------
1  | "Hans","Müller","Beispielstraße1","Berlin"

Wenn ich das sinnvoll auf einer Homepage darstellen möchte, muss ich den Tag wieder auseinander nehmen. Klar für jemanden der tagtäglich programmiert eine Kleinigkeit. Für jemand der nur ab und zu programiert komplizierter, als wenn er nur zwei verschachtelte Schleifen nimmt und ein indiziert Array abfragt.