Message-ID: <51FD78ED.4020301@gmail.com>
Date: Sat, 03 Aug 2013 15:41:01 -0600
From: Rob Sargentg <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:17.0) Gecko/20130623 Thunderbird/17.0.7
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Presenting data in 5 Rows & 5 Cols for 25 specific values
References: 
 <CAAmqg_BorkCvgY2NzAYkr=9ysrzswOGTtKdtsZ5H4MCpHj5ZVw@mail.gmail.com>
In-Reply-To: 
 <CAAmqg_BorkCvgY2NzAYkr=9ysrzswOGTtKdtsZ5H4MCpHj5ZVw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 08/03/2013 07:26 AM, F Bax wrote:
> I have a table containing tasks completed in a game I'm playing.  The 
> game includes an extra BINGO Challenge where each cell of standard 
> BINGO card contains a particular task to be completed.  The goal is 
> score a BINGO (row, column, diagonal) by completing five (or more) 
> tasks from the BINGO cards.  My task table contains more tasks 
> completed than the one included in the BINGO challenge.
>
> SELECT task, CASE WHEN task='Task27' THEN 'R1C1' WHEN task='Task32' 
> THEN 'R1C2' ... WHEN task='Task94' THEN 'R5C5' END AS bingo FROM tasks 
> WHERE bingo IS NOT NULL;
>
> This query will retrieve all tasks related to the BINGO that I have 
> completed and present them in a simple list.  I would like to arrange 
> the tasks as a BINGO card; so that I can easily see my progress on 
> various rows & columns working toward a BINGO.
>
> Any suggestions?
>
> BONUS points will be awarded if the query displays a row with 5 NULL 
> values if no tasks are completed in that row.
I don't think you've fully described your tables so I've ad libbed a 
little.  This is not a complete solution (and certainly no bonus point) 
but it should give you one starting point. Your CTE will have to deal 
with transforming the RmCn into something more useful
create table bingo (ttype text, username text, task text, r int, c int);
begin;
insert into bingo values
('bingo','me','task0',0, 0),
('bingo','me','task1',0, 1),
('bingo','me','task2',0, 2),
('bingo','me','task3',0, 3),
('bingo','me','task4',0, 4),
('bingo','me','task5',1, 0),
('bingo','me','task6',1, 1),
('bingo','me','task7',1, 2),
('bingo','me','task8',1, 3),
('bingo','me','task9',1, 4),
('bingo','me','task10',2, 0),
...
('bingo','me','task71',14, 1),
('bingo','me','task72',14, 2),
('bingo','me','task73',14, 3),
('bingo','me','task74',14, 4)

with bingoline as (
select r, array_agg(task) as taskline from bingo where mod(r,5) = 0 and 
username = 'me' group by r
union                                                            --
select r, array_agg(task) as taskline from bingo where mod(r,5) = 1 and 
username = 'me' group by r
union                                                            --
select r, array_agg(task) as taskline from bingo where mod(r,5) = 2 and 
username = 'me' group by r
union                                                            --
select r, array_agg(task) as taskline from bingo where mod(r,5) = 3 and 
username = 'me' group by r
union                                                            --
select r, array_agg(task) as taskline from bingo where mod(r,5) = 4 and 
username = 'me' group by r
)
select array_to_string(bl.taskline, '|', 'nil') from bingoline bl order 
by bl.r;
;

          Bingo Card
------------------------------------
  task0|task1|task2|task3|task4
  task5|task6|task7|task8|task9
  task10|task11|task12|task13|task14
  task15|task16|task17|task18|task19
  task20|task21|task22|task23|task24
  task25|task26|task27|task28|task29
  task30|task31|task32|task33|task34
  task35|task36|task37|task38|task39
  task40|task41|task42|task43|task44
  task45|task46|task47|task48|task49
  task50|task51|task52|task53|task54
  task55|task56|task57|task58|task59
  task60|task61|task62|task63|task64
  task65|task66|task67|task68|task69
  task70|task71|task72|task73|task74
(15 rows)


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql