Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQKjs-0003F2-Os for pgsql-sql@arkaria.postgresql.org; Wed, 19 Mar 2014 17:57:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WQKjs-00072O-5e for pgsql-sql@arkaria.postgresql.org; Wed, 19 Mar 2014 17:57:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQKjr-00072H-40 for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 17:57:15 +0000 Received: from plane.gmane.org ([80.91.229.3]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQKjo-0000Sc-JK for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 17:57:14 +0000 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1WQKjl-0004nv-TP for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 18:57:09 +0100 Received: from static.187.232.9.176.clients.your-server.de ([176.9.232.187]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 19 Mar 2014 18:57:09 +0100 Received: from hari.fuchs by static.187.232.9.176.clients.your-server.de with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 19 Mar 2014 18:57:09 +0100 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: hari.fuchs@gmail.com Subject: Re: Table results format - should I use crosstab? If so, how? Date: Wed, 19 Mar 2014 18:56:58 +0100 Lines: 25 Message-ID: <87wqfqrs0l.fsf@hf.protecting.net> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: static.187.232.9.176.clients.your-server.de X-Archive: encrypt X-PGP-Fingerprint: 09 4E C4 A2 B2 C5 33 1A 79 80 5D 39 BD 9B 89 39 Face: iVBORw0KGgoAAAANSUhEUgAAADAAAAAwBAMAAAClLOS0AAAAAXNSR0IArs4c6QAAABVQTFRFz5WF X0ZB9ObgFhEQl2ti/////v/+DXadyAAAAAFiS0dEAIgFHUgAAAAJcEhZcwAACxMAAAsTAQCanBgA AAAHdElNRQfcBRIQBAd99pVHAAABs0lEQVQ4y12UTXPCIBCGsUXPXaG522rPRtI7jFvP1k44ezD5 /z+hu2wgxB0zGJ7ZrzcLqi829P0YZSVTZT9+6AB2w/+GBbg7tu4wuWcQ905sK5GKx81MwG0WIO7y vjtVIFYOrhv6YQYPN9ulCjW+VuBYgSoS2WEC1O0cqaWnicXjk7cMAP3AuNNYkp8dgEWLgAjg2lLV zbS0r7xSivQyKYkAckAlhgTWBbRL8JPBVYBPIBjXFMCpBegEYg4lQGWPUymXgC8A3HHMgBrLQMMS mHYiwYHrMtizFMlHAxVoupxj5yiH5jSBsoE5ZvAwgMmH9GCPt9KHscHCCqgiWoPZZnA3Fj3ph8Gy jAvguWkEBjiDm7GpUpukRNPQlE592CSTD5hK/ingnAB4lPVStAppw6rUi4YNDYiAlYR6oWgMDsMU qt8lEHwqV2/ngXuAlIvIS1NNO3gvgPt8Boja07MEv5YBO2mptgIkLWtCy6E+nCQGigUY6zMIKVYC 28Xh/KM4XnsekuWp7Rlwdnh/BsDdFYfqZmj5YyB8y41RgSvlp3rlyojDDEgwmp319DJWIJLuX0MG //igAGCKkEYtAAAAAElFTkSuQmCC User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.4 (gnu/linux) Cancel-Lock: sha1:5fvsNyPUbfdmnmU0bTxBIo6+Oh8= X-Pg-Spam-Score: -1.0 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org Jennifer Mackown writes: > Hi, > I have a problem with getting a table to display in the way I want it to. It's one of those things that looks so simple I should be able to do it in 5 minutes, but I've been working on it all afternoon and I'm getting nowhere!! > What I have is the following: > Date Firstday Lastday2014/03/12 1 12014/03/18 1 02014/03/19 0 12014/03/21 1 1 > > And what I need to see is this: > Firstday Lastday2014/03/12 2013/03/122014/03/18 2013/03/192014/03/21 2013/03/21 > > > Can anyone help? WITH tmp (id, firstday, lastday) AS ( SELECT row_number() OVER (PARTITION BY t1.date ORDER BY t2.date), t1.date, t2.date FROM tbl t1 JOIN tbl t2 ON t2.date >= t1.date AND t2.lastday = 1 WHERE t1.firstday = 1 ) SELECT id, firstday, lastday FROM tmp WHERE id = 1 ORDER BY firstday, lastday -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql