Is this a PIVOT?

[RRinTetons]

SQL Server 2008 R2

Generally I let the front end do pivots for me, but in this case I need to pass data to the front end pre-pivoted, at least I think that's the way to do it.

I have data like this:

[tt]
Holiday[tab][tab][tab][tab][tab]DateDate
Christmas[tab][tab][tab][tab][tab]12/25/2013
Christmas[tab][tab][tab][tab][tab]12/25/2014
Easter[tab][tab][tab][tab][tab][tab]3/31/2013
Easter[tab][tab][tab][tab][tab][tab]4/20/2014
Independence Day[tab][tab][tab][tab]7/4/2013
Independence Day[tab][tab][tab][tab]7/4/2014
Martin Luther King Day[tab][tab]1/21/2013
Martin Luther King Day[tab][tab]1/20/2014
New Years Day[tab][tab][tab][tab]1/1/2013
New Years Day[tab][tab][tab][tab]1/1/2014
Opening Day[tab][tab][tab][tab]11/30/2013
Opening Day[tab][tab][tab][tab] 11/29/2014
Presidents Day[tab][tab][tab][tab]2/18/2013
Presidents Day[tab][tab][tab][tab]2/17/2014
Thanksgiving Day[tab][tab][tab][tab]11/28/2013
Thanksgiving Day[tab][tab][tab][tab]11/27/2014
[/tt]
That needs to look like this:

Holiday[tab][tab][tab][tab][tab]First[tab][tab][tab][tab][tab][tab]Next+1
Christmas[tab][tab][tab][tab][tab]12/25/2013[tab][tab][tab][tab]12/25/2014
Easter[tab][tab][tab][tab][tab][tab]3/31/2013[tab][tab][tab][tab]4/20/2014
Independence Day[tab][tab][tab]7/4/2013[tab][tab][tab][tab]7/4/2014
MLK[tab][tab][tab][tab][tab][tab][tab] 1/21/2013[tab][tab][tab][tab]1/20/2014
New Year[tab][tab][tab][tab][tab]1/1/2013[tab][tab][tab][tab]1/1/2014
Opening Day[tab][tab][tab][tab][tab]11/30/2013[tab][tab][tab][tab]11/29/2014
Presidents Day[tab][tab][tab][tab][tab]2/18/2013[tab][tab][tab]2/17/2014
Thanksgiving[tab][tab][tab][tab][tab]11/28/2013[tab][tab][tab]11/27/2014

Except that there are 10 dates (First, Next+1, Next+2, Next+3, etc., a known number and always the same for all holidays) for each holiday and another dozen holidays.

That looks like a PIVOT query, but I have to admit I have always found the PIVOT syntax pretty much perfectly opaque. OTOH, if figuring it out will solve this problem, then I'll do it.

Is that the way to do it?

(PS - is there an 'insert table' function on this board that I've been missing all this time?)

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[RRinTetons]

No, Richard, it's not, at least not obviously. There's no aggregation going on, so it doesn't really lend itself to PIVOT.

However, it might be some kind of a recursive CTE. If only you knew how to do those! But, a smart fellow like you who understands recursion as a concept might be able to figure it out....

How does that sound?

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort