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

Jennifer Mackown <niftyshellsuit@outlook.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
simpleI 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.dateFROM 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