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

Jennifer Mackown <niftyshellsuit(at)outlook(dot)com> 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