X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 842599DC87B for ; Thu, 9 Feb 2006 05:35:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93802-10 for ; Thu, 9 Feb 2006 05:35:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.attiksystem.ch (f29.attiksystem.ch [212.147.59.29]) by postgresql.org (Postfix) with ESMTP id 723F89DC851 for ; Thu, 9 Feb 2006 05:35:02 -0400 (AST) Received: from poweredge.attiksystem.ch (poweredge.attiksystem.ch [10.0.0.29]) by mail.attiksystem.ch (8.12.11/8.12.11) with ESMTP id k199Z3vB012502 for ; Thu, 9 Feb 2006 10:35:03 +0100 (CET) (envelope-from philippe.lang@attiksystem.ch) Subject: Concatenate strings using GROUP BY MIME-Version: 1.0 Content-Class: urn:content-classes:message Content-Type: multipart/signed; micalg=SHA1; protocol="application/x-pkcs7-signature"; boundary="----=_NextPart_000_0000_01C62D64.7B64C920" Date: Thu, 9 Feb 2006 10:35:03 +0100 X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 Message-ID: <6C0CF58A187DA5479245E0830AF84F42143B77@poweredge.attiksystem.ch> X-MS-Has-Attach: yes X-MS-TNEF-Correlator: Thread-Topic: Concatenate strings using GROUP BY Thread-Index: AcYtXBk5lBdN8lpVTHO3zg8Xa5NPqg== From: "Philippe Lang" To: X-Scanned-By: MIMEDefang 2.49 on 10.0.0.111 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200602/96 X-Sequence-Number: 24106 This is a multi-part message in MIME format. ------=_NextPart_000_0000_01C62D64.7B64C920 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, Let's say we have a query like: SELECT id, fk, str FROM foo ... which returns: id fk str --------------- 1 1 a 2 1 b 3 2 c Is there a pure SQL way of contactenating str values into a single string, and get instead: id fk str --------------- 1 1 a/b 2 12 c I was thinking of something like: SELECT id, fk, CONCAT(str || '/') FROM FOO GROUP BY fk Do we have to use PL/PGSQL for that? Thanks, ---------------------------------- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26) 422 13 76 Email: philippe.lang@attiksystem.ch ------=_NextPart_000_0000_01C62D64.7B64C920 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIII/zCCAocw ggHwoAMCAQICEFpaIYarXomyBeuKGjRG04swDQYJKoZIhvcNAQEEBQAwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1MTIyMDEzNDAyNloXDTA2MTIyMDEzNDAy NlowZzENMAsGA1UEBBMETGFuZzERMA8GA1UEKhMIUGhpbGlwcGUxFjAUBgNVBAMTDVBoaWxpcHBl IExhbmcxKzApBgkqhkiG9w0BCQEWHHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwgZ8wDQYJ KoZIhvcNAQEBBQADgY0AMIGJAoGBAJsLuUD6Q3LdQfy6UKzYyhLAyhzocE3Lj7oaJLBvFQk0rQHf j14K6A39OW5pBil2aw/B93JalSJVkasS3FsjAFs1LvQS8xY0mgB2CtthT3FajV0bB424LQdZ2KhG tHbPEm/68S0ARXlTZaD4I5ixwFX+Zaa0uuPFhWfZems0B7nBAgMBAAGjOTA3MCcGA1UdEQQgMB6B HHBoaWxpcHBlLmxhbmdAYXR0aWtzeXN0ZW0uY2gwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQQF AAOBgQA3w0be0GoD+dBCQ0JuV0l2gpBC8KcKaym1zGiRLn7C1ojGuXsw/TShxor7f63H3CUZq+aL zdLtUpFm4a/LdO/1GLKs4ZJSY1WKzeNHiyXGjnkvuzKjRwzVA94McLeBpg8fDocaH3Z0Go9dg/f6 d0sMTmKAxRDYMeNtWjUuAGod+DCCAy0wggKWoAMCAQICAQAwDQYJKoZIhvcNAQEEBQAwgdExCzAJ BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgG A1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMg RGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3 DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw05NjAxMDEwMDAwMDBaFw0yMDEy MzEyMzU5NTlaMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wgZ8wDQYJ KoZIhvcNAQEBBQADgY0AMIGJAoGBANRp19SwlGRbcelH2AxRtupykbCEXn0tDY97Et+FJXUodDpC LGMnn5V7S+9+GYcdhuqj3bnOlmQawhRuRKx85o/oTQ9xH0A4pgCjh3j2+ZSGXq3qwF5269kUo11u enwMpUtVfwYZKX+emibVars4JAhqmMex2qOYkf152+VaxBy5AgMBAAGjEzARMA8GA1UdEwEB/wQF MAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAx+ySfk749ZalZ2IqpPBNEWDQb41gWGGsJrtSNVwIzzD7 qEqWih9iQiOMFw/0umScF6xHKd+dmF7SbGBxXKKs3Hnj524ARx+1DSjoAp3kmv0T9KbZfLH43F8j JgmRgHPQFBveQ6mDJfLmnC8Vyv6mq4oHdYsM3VGEa+T40c53ooEwggM/MIICqKADAgECAgENMA0G CSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYD VQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0 aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJl ZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcN MDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5o wHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH 5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7 AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8v Y3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYw KQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUA A4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZ Ohl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NW IXiC3CEZNd4ksdMdRv9dX2VPMYIC+DCCAvQCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMc VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZy ZWVtYWlsIElzc3VpbmcgQ0ECEFpaIYarXomyBeuKGjRG04swCQYFKw4DAhoFAKCCAdgwGAYJKoZI hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwMjA5MDkzNTAxWjAjBgkqhkiG 9w0BCQQxFgQUBaqI0V3QxToaaCkBHtPNjmXbVtUwZwYJKoZIhvcNAQkPMVowWDAKBggqhkiG9w0D BzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwICASgw BwYFKw4DAhowCgYIKoZIhvcNAgUwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMCWkExJTAj BgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJz b25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBaWiGGq16JsgXriho0RtOLMIGHBgsqhkiG9w0BCRAC CzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0 ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhBaWiGGq16J sgXriho0RtOLMA0GCSqGSIb3DQEBAQUABIGACutTS69kNQgyElcCgxfG6uv2WvRjciUmfc2vVvgM POJm1vack/jiFk5fyagd3Uaw/YAsFgck2W5+StKxkTYa+Uz0TE6kmzP0ECneuDVCoV1PC9GQBCWb O+O/GGEoMSXszjU0+1No6VrfzVSCgo7+p8TpSNUV7jKTzlK2LG9dLSQAAAAAAAA= ------=_NextPart_000_0000_01C62D64.7B64C920--