Re: Table results format - should I use crosstab? If so, how?

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