>Ok with the help from a guy at Expert Exchange i have it running correctly. Still, i need to save the result to a table in my db ( i delete it prior to running the pivot ) but tried entering INTO myPivot in my dynamic query and it triggers a syntax error. This is the script:
>

>
>SELECT @DynamicPivotQuery =
>N'SELECT Station, CarGroup, CarNo,' + @ColumnName +
>'FROM
>(
>	SELECT R.PickUpStation as Station, R.CarGroup, R.CarNo, REPLACE(CONVERT(VARCHAR(6), D.[Date], 100), '' '', '''') AS DateString
>	FROM dbo.FleetTrack AS R
>	INNER JOIN #Date AS D
>		ON R.PickUpDate <= D.Date
>		AND R.DropOffDate >= D.Date
>	) AS Reservation
>PIVOT
>(
>	COUNT(DateString)
>	FOR DateString IN (' + @ColumnName + ')
>) AS pvt
>ORDER BY pvt.Station, pvt.CarGroup, pvt.CarNo'
>EXECUTE sp_executesql @DynamicPivotQuery; 
>

>
>How do i create a table into my DB with the results of the above ? ( in pvt )
>
>
>>Hi
>>I need to create a table with 2 character fields ( field1, field2 ) then fields of BIT type, default to 0 (false) , named
>>[0501], [0502] [0503].....and up to [1029], [1030], [1031]
>>Where 0501 represents May 1, etc... ( format mmdd)
>>Is there some way to program sql server to build this?
>>
>>Thanks
>>Jaime

One possibility is to use INTO clause which will produce an actual table in your DB. You should run this statement only once then, e.g.

SELECT @DynamicPivotQuery =
N'SELECT Station, CarGroup, CarNo,' + @ColumnNames +
'  INTO myNewPivotTable

FROM
(
	SELECT R.PickUpStation as Station, R.CarGroup, R.CarNo, REPLACE(CONVERT(VARCHAR(6), D.[Date], 100), '' '', '''') AS DateString
       
	FROM dbo.FleetTrack AS R
	INNER JOIN #Date AS D
		ON R.PickUpDate <= D.Date
		AND R.DropOffDate >= D.Date
	) AS Reservation
PIVOT
(
	COUNT(DateString)
	FOR DateString IN (' + @ColumnNames + ')
) AS pvt
ORDER BY pvt.Station, pvt.CarGroup, pvt.CarNo'

EXECUTE sp_executesql @DynamicPivotQuery;