Re: Tabelle umgruppieren ?
Nicola Tiling <[email protected]> Sun, 27 Oct 2019 19:38:07 +0100
| Newsgroups | gmane.comp.db.postgresql.german |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_DB9796F9-B60A-497F-AA45-E763839F15EB Content-Type: multipart/alternative; boundary="Apple-Mail=_D63D14DC-C121-45D9-8EAA-5B2307C72E71" --Apple-Mail=_D63D14DC-C121-45D9-8EAA-5B2307C72E71 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Moin Herzlichen Dank an Euch! Manchmal muss man gedanklich ja mal in eine = andere Richtung gestupst werden! Die L=C3=B6sung mit dem =E2=80=9Estring_agg=E2=80=9C von dir, Andreas = funktionierte auf Anhieb. Die mit dem Self join von dir Gunnar fast. Hier mochte es das "ORDER BY a.switch=E2=80=9C" in der ersten Zeile = nicht. Au=C3=9Ferdem referenzierten die Ger=C3=A4te auch auf sich selbst = wenn noch kein Gegen=C3=BCber erfasst ist. Ich habe das jetzt so gemacht: SELECT * FROM ( SELECT DISTINCT ON (a.kabel_nr) a.kabel_nr ,a.switch AS switch_a ,a.port_nr AS port_nr_a ,b.switch AS switch_b ,b.port_nr AS port_nr_b FROM netzwerkdoku a JOIN netzwerkdoku b ON a.kabel_nr =3D b.kabel_nr AND a.switch !=3D b.switch ) t ORDER BY t.switch_a, t.port_a Gr=C3=BC=C3=9Fe Nicola > Am 27.10.2019 um 17:24 schrieb Andreas Kretschmer = <[email protected]>: >=20 > das ginge z.B. so: >=20 > test=3D*# select * from kabel ; > switch | port | kabel_nr > ----------+------+---------- > cisco1 | 4 | 12 > cisco1 | 5 | 13 > cisco1 | 6 | 14 > cisco2 | 6 | 14 > cisco2 | 48 | 10 > juniper1 | 8 | 12 > juniper1 | 9 | 13 > juniper1 | 42 | 10 > (8 rows) >=20 > test=3D*# select kabel_nr, string_agg(switch || ' ' || port::text, ' = <-> ') from kabel group by kabel_nr; > kabel_nr | string_agg > ----------+--------------------------- > 10 | cisco2 48 <-> juniper1 42 > 14 | cisco1 6 <-> cisco2 6 > 13 | cisco1 5 <-> juniper1 9 > 12 | cisco1 4 <-> juniper1 8 > (4 rows) >=20 > test=3D*# >=20 > Am 27.10.2019 um 13:16 schrieb Gunnar Nick Bluth = <[email protected]>: >=20 > Das dient ja auch zum "pivotieren"... hier brauchst du einen = schlichten > self-join. >=20 > SELECT DISTINCT ON (a.kabel_nr ORDER BY a.switch) > a.kabel_nr > ,a.switch AS switch_a > ,a.port_nr AS port_nr_a > ,b.switch AS switch_b > ,b.port_nr AS port_nr_b > FROM netzwerkdoku a > JOIN netzwerkdoku b > ON a.kabel_nr =3D b.kabel_nr; >=20 > sollte m.E. dein gew=C3=BCnschtes Ergebnis liefern. >=20 --Apple-Mail=_D63D14DC-C121-45D9-8EAA-5B2307C72E71 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 <html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; = charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;" = class=3D"">Moin<div class=3D""><br class=3D""></div><div class=3D""><span = style=3D"font-size: 14px;" class=3D"">Herzlichen Dank an Euch! Manchmal = muss man gedanklich ja mal in eine andere Richtung gestupst = werden!</span></div><div class=3D""><span style=3D"font-size: 14px;" = class=3D""><br class=3D""></span></div><div class=3D""><span = style=3D"font-size: 14px;" class=3D"">Die L=C3=B6sung mit dem = =E2=80=9Estring_agg=E2=80=9C von dir, Andreas funktionierte auf Anhieb. = Die mit dem Self join von dir Gunnar fast.</span></div><div = class=3D""><span style=3D"font-size: 14px;" class=3D""><br = class=3D""></span></div><div class=3D""><span style=3D"font-size: 14px;" = class=3D"">Hier mochte es das "<font face=3D"Courier" class=3D"">ORDER = BY a.switch=E2=80=9C</font>" in der ersten Zeile nicht. Au=C3=9Ferdem = referenzierten die Ger=C3=A4te auch auf sich selbst wenn noch kein = Gegen=C3=BCber erfasst ist.</span></div><div class=3D""><span = style=3D"font-size: 14px;" class=3D""><br class=3D""></span></div><div = class=3D""><span style=3D"font-size: 14px;" class=3D"">Ich habe das = jetzt so gemacht:</span></div><div class=3D""><span style=3D"font-size: = 14px;" class=3D""><br class=3D""></span></div><div class=3D""><div = class=3D""><span class=3D"" style=3D"font-family: Courier; font-size: = 13px; float: none; display: inline !important;">SELECT * FROM = (</span></div></div><blockquote style=3D"margin: 0 0 0 40px; border: = none; padding: 0px;" class=3D""><div class=3D""><div class=3D""><span = class=3D"" style=3D"font-family: Courier; font-size: 13px; float: none; = display: inline !important;">SELECT DISTINCT ON = (a.kabel_nr)</span></div></div><div class=3D""><div class=3D""><span = class=3D"Apple-tab-span" style=3D"font-family: Courier; font-size: 13px; = white-space: pre;"> </span><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline = !important;"> a.kabel_nr</span></div></div><div class=3D""><div = class=3D""><span class=3D"Apple-tab-span" style=3D"font-family: Courier; = font-size: 13px; white-space: pre;"> </span><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">,a.switch AS switch_a</span></div></div><div = class=3D""><div class=3D""><span class=3D"Apple-tab-span" = style=3D"font-family: Courier; font-size: 13px; white-space: pre;"> = </span><span class=3D"" style=3D"font-family: Courier; font-size: 13px; = float: none; display: inline !important;">,a.port_nr AS = port_nr_a</span></div></div><div class=3D""><div class=3D""><span = class=3D"Apple-tab-span" style=3D"font-family: Courier; font-size: 13px; = white-space: pre;"> </span><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline = !important;">,b.switch AS switch_b</span></div></div><div class=3D""><div = class=3D""><span class=3D"Apple-tab-span" style=3D"font-family: Courier; = font-size: 13px; white-space: pre;"> </span><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">,b.port_nr AS port_nr_b</span></div></div><div = class=3D""><div class=3D""><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline !important;">FROM = netzwerkdoku a </span><span style=3D"font-family: Courier; = font-size: 13px;" class=3D"">JOIN netzwerkdoku b</span></div></div><div = class=3D""><div class=3D""><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline !important;">ON = a.kabel_nr =3D b.kabel_nr AND a.switch !=3D = b.switch</span></div></div><div class=3D""><span style=3D"font-family: = Courier; font-size: 13px;" class=3D"">) t</span></div></blockquote><div = class=3D""><span style=3D"font-family: Courier; font-size: 13px;" = class=3D"">ORDER BY t.switch_a, t.port_a</span></div><div = class=3D""><div><br = class=3D""></div><div>Gr=C3=BC=C3=9Fe</div><div>Nicola</div><div><br = class=3D""><blockquote type=3D"cite" class=3D""><div class=3D"">Am = 27.10.2019 um 17:24 schrieb Andreas Kretschmer <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>>:</div><div class=3D""><div = class=3D""><br class=3D"">das ginge z.B. so:<br class=3D""><br = class=3D"">test=3D*# select * from kabel ;<br class=3D""> = switch | port | kabel_nr<br = class=3D"">----------+------+----------<br = class=3D""> cisco1 | 4 = | 12<br = class=3D""> cisco1 | 5 = | 13<br = class=3D""> cisco1 | 6 = | 14<br = class=3D""> cisco2 | 6 = | 14<br = class=3D""> cisco2 | 48 = | 10<br class=3D""> juniper1 = | 8 | 12<br = class=3D""> juniper1 | 9 = | 13<br class=3D""> juniper1 = | 42 | 10<br class=3D"">(8= rows)<br class=3D""><br class=3D"">test=3D*# select kabel_nr, = string_agg(switch || ' ' || port::text, ' <-> ') from kabel group = by kabel_nr;<br class=3D""> kabel_nr = | string_agg<br = class=3D"">----------+---------------------------<br = class=3D""> 10 | cisco2 48 <-> = juniper1 42<br class=3D""> 14 | = cisco1 6 <-> cisco2 6<br = class=3D""> 13 | cisco1 5 <-> = juniper1 9<br class=3D""> 12 | = cisco1 4 <-> juniper1 8<br class=3D"">(4 rows)<br class=3D""><br = class=3D"">test=3D*#<br class=3D""><br = class=3D""></div></div></blockquote><br class=3D""><blockquote = type=3D"cite" class=3D""><div class=3D""><div class=3D"">Am 27.10.2019 = um 13:16 schrieb Gunnar Nick Bluth <<a = href=3D"mailto:[email protected]" = class=3D"">[email protected]</a>>:</div></div></blockquote><bloc= kquote type=3D"cite" class=3D""><div class=3D""><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">Das dient ja auch zum "pivotieren"... hier brauchst = du einen schlichten</span><br class=3D"" style=3D"font-family: Courier; = font-size: 13px;"><span class=3D"" style=3D"font-family: Courier; = font-size: 13px; float: none; display: inline = !important;">self-join.</span><br class=3D"" style=3D"font-family: = Courier; font-size: 13px;"><br class=3D""><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">SELECT DISTINCT ON (a.kabel_nr ORDER BY = a.switch)</span><br class=3D"" style=3D"font-family: Courier; font-size: = 13px;"><span class=3D"Apple-tab-span" style=3D"font-family: Courier; = font-size: 13px; white-space: pre;"> </span><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;"> a.kabel_nr</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span = class=3D"Apple-tab-span" style=3D"font-family: Courier; font-size: 13px; = white-space: pre;"> </span><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline = !important;">,a.switch AS switch_a</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span = class=3D"Apple-tab-span" style=3D"font-family: Courier; font-size: 13px; = white-space: pre;"> </span><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline = !important;">,a.port_nr AS port_nr_a</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span = class=3D"Apple-tab-span" style=3D"font-family: Courier; font-size: 13px; = white-space: pre;"> </span><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline = !important;">,b.switch AS switch_b</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span = class=3D"Apple-tab-span" style=3D"font-family: Courier; font-size: 13px; = white-space: pre;"> </span><span class=3D"" style=3D"font-family: = Courier; font-size: 13px; float: none; display: inline = !important;">,b.port_nr AS port_nr_b</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">FROM netzwerkdoku a</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">JOIN netzwerkdoku b</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">ON a.kabel_nr =3D b.kabel_nr;</span><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><br class=3D"" = style=3D"font-family: Courier; font-size: 13px;"><span class=3D"" = style=3D"font-family: Courier; font-size: 13px; float: none; display: = inline !important;">sollte m.E. dein gew=C3=BCnschtes Ergebnis = liefern.</span></div></blockquote><blockquote type=3D"cite" = class=3D""><div class=3D""><div class=3D""><br = class=3D""></div></div></blockquote></div><br = class=3D""></div></body></html>= --Apple-Mail=_D63D14DC-C121-45D9-8EAA-5B2307C72E71-- --Apple-Mail=_DB9796F9-B60A-497F-AA45-E763839F15EB Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename=signature.asc Content-Type: application/pgp-signature; name=signature.asc Content-Description: Message signed with OpenPGP -----BEGIN PGP SIGNATURE----- iF0EARECAB0WIQQBa8GmpW8Jduii5iTRZnUyLhNRuQUCXbXkDwAKCRDRZnUyLhNR uRXxAJ9x+1MlSOakhl2u9RASEzXvPvQ0ugCfYuoMuBhjtPOjcB3qU5RmBsgFPvQ= =WKwT -----END PGP SIGNATURE----- --Apple-Mail=_DB9796F9-B60A-497F-AA45-E763839F15EB--