From niftyshellsuit@outlook.com Wed Mar 19 14:59:04 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQHxQ-0005Fd-1U for pgsql-sql@arkaria.postgresql.org; Wed, 19 Mar 2014 14:59:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WQHxP-0002h8-IA for pgsql-sql@arkaria.postgresql.org; Wed, 19 Mar 2014 14:59:03 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQHxO-0002h2-Ol for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 14:59:02 +0000 Received: from dub0-omc2-s24.dub0.hotmail.com ([157.55.1.163]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQHxL-0004uz-0i for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 14:59:02 +0000 Received: from DUB115-W133 ([157.55.1.136]) by dub0-omc2-s24.dub0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Wed, 19 Mar 2014 07:58:57 -0700 X-TMN: [3vuOn0ZsyuDoCtHB7etr7yjsoYL2sG6i] X-Originating-Email: [niftyshellsuit@outlook.com] Message-ID: Content-Type: multipart/alternative; boundary="_d0cc8a47-ff63-43ce-853a-190bb92f3ad2_" From: Jennifer Mackown To: "pgsql-sql@postgresql.org" Subject: Table results format - should I use crosstab? If so, how? Date: Wed, 19 Mar 2014 14:58:57 +0000 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 19 Mar 2014 14:58:57.0538 (UTC) FILETIME=[C108A620:01CF4383] X-Pg-Spam-Score: 0.8 (/) 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 --_d0cc8a47-ff63-43ce-853a-190bb92f3ad2_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi=2C=20 I have a problem with getting a table to display in the way I want it to. I= t's one of those things that looks so simple I should be able to do it in 5= minutes=2C but I've been working on it all afternoon and I'm getting nowhe= re!! What I have is the following: Date Firstday Lastday2014/03/12 1 12= 014/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 201= 3/03/192014/03/21 2013/03/21 Can anyone help? Thanks=2C=20 Jennifer = --_d0cc8a47-ff63-43ce-853a-190bb92f3ad2_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi=2C =3B

I have a problem with getting a table to display in the way I w= ant it to. It's one of those things that looks so simple I should be able t= o do it in 5 minutes=2C but I've been working on it all afternoon and I'm g= etting nowhere!!

What I have is the following:

Date =3B =3B =3B =3B =3B &nb= sp=3B =3B Firstday =3B =3BLastday

2014/03/12 = =3B =3B =3B =3B1 =3B =3B =3B =3B = =3B =3B =3B =3B1

2014/03/18 =3B =3B &nbs= p=3B =3B1 =3B =3B =3B =3B =3B =3B = =3B =3B0

2014/03/19 =3B =3B =3B =3B0 &nb= sp=3B =3B =3B =3B =3B =3B =3B =3B1

2014/03/21 =3B =3B =3B =3B1 =3B =3B &nbs= p=3B =3B =3B =3B =3B =3B1

And what I need to see is this:

Fi= rstday =3B =3B =3B =3B =3B =3B Lastday

<= div>2014/03/12 =3B =3B =3B 2013/03/12

2014/03/18 &= nbsp=3B =3B =3B 2013/03/19

2014/03/21 =3B =3B= =3B 2013/03/21

Can anyone help?

Thanks=2C =3B

Jennifer =3B

= --_d0cc8a47-ff63-43ce-853a-190bb92f3ad2_-- From polobo@yahoo.com Wed Mar 19 15:47:56 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQIii-0006zQ-4e for pgsql-sql@arkaria.postgresql.org; Wed, 19 Mar 2014 15:47:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WQIih-0003Q3-GE for pgsql-sql@arkaria.postgresql.org; Wed, 19 Mar 2014 15:47:55 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQIig-0003Pw-Eg for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 15:47:54 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQIid-0005pl-PP for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 15:47:53 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1WQIic-0003I5-R8 for pgsql-sql@postgresql.org; Wed, 19 Mar 2014 08:47:50 -0700 Date: Wed, 19 Mar 2014 08:47:50 -0700 (PDT) From: David Johnston To: pgsql-sql@postgresql.org Message-ID: <1395244070835-5796803.post@n5.nabble.com> In-Reply-To: References: Subject: Re: Table results format - should I use crosstab? If so, how? MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 4.5 (++++) 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 wrote > 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 WITH data (dt, isfirst, islast) AS ( --setup data VALUES ('2014-03-12'::date, true, true), ('2013-03-18', true, false), ('2013-03-19', false, true), ('2013-03-21', true, true) ) , explode AS ( --need to convert columns to rows; use UNION ALL to do this SELECT dt, 1 AS pos FROM data WHERE isfirst --all start rows UNION ALL SELECT dt, 2 AS pos FROM data WHERE islast --all end rows ) , ordered AS ( --need to arrange the rows so start comes before end SELECT * FROM explode ORDER BY dt ASC, pos ) SELECT firstday, lastday FROM ( --then for each end row in the pair get the immediately prior row as its start SELECT pos, dt AS lastday, lag(dt) OVER () AS firstday FROM ordered ) calc WHERE pos = 2 ORDER BY firstday DESC --and only display the end rows ; This directly solves the problem, however: 1) Start & End dates must be defined in pairs (none missing and no extra dates in between) 2) There can be no overlapping ranges Ideally you would have some kind of identifier attached to every start date and a corresponding identifier on the matching end date. Partitioning on that identifier and taking the first and last date found would be the most stable method. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Table-results-format-should-I-use-crosstab-If-so-how-tp5796797p5796803.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql From hari.fuchs@gmail.com Wed Mar 19 17:57:16 2014 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