Trying To Transpose And Collapse Data...

I'm relatively new to this, so bear with me here.  (SQL Server 2005 Express, Datatypes are all varchar, int or money, nothing crazy...)

I currently have a table (not designed by me...) which looks like this:

ProjectID            Months           Expenses





3214                JAN                    45.67

3214                 MAR                    56.78

1234                 JAN                     78.99

4567                  MAY                    43.56

 

And so on....  And I need this:

Project ID       Jan         Feb    Mar   Apr    May   Jun  etc....





3214              45.67                56.78

1234               78.99

4567                                                              43.56

 

I had attempted to do it using this code (really sloppy... I know.  Beginner's attempt...)





DECLARE @Months varchar(4)

DECLARE @Counter int

DECLARE @Rowcount int

EXEC @RowCount=dbo.ReturnRowCount

SET @Counter = 0

WHILE @Counter <= @RowCount

BEGIN

SET @Counter=@Counter+1

SET @Months=(SELECT Months FROM TestData WHERE RowNum=@Counter)

SELECT @Months

WHILE @Months='JAN'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Jan)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='FEB'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Feb)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='MAR'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Mar)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='APR'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Apr)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='MAY'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, May)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='JUN'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Jun)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='JUL'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Jul)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='AUG'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Aug)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='SEP'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Sep)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='OCT'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Oct)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='NOV'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Nov)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

WHILE @Months='DEC'

BEGIN

INSERT INTO SusansOutputTable (ProjectID, Dec)

SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum

SET @Months=''

END

SET @Months=''

END

SELECT RowNum, ProjectID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec FROM SusansOutputTable







I also tried using IF statement instead of the imbedded WHILE statement and had the same result. What it's returning is 98,765 results of the same thing, with the exception of the last 25 rows, which return what I expected (which still needs to be collapsed...)  I know there HAS to be an easier way to do this, I'm afraid it might be a bit beyond me though, any help?

 

Ps.  ReturnRowCount function just returns the row count of the base table, assuming a rowid column with an IDENTITY int variable, which I can safely assume in this case.  Also, the counter seems to work fine, but something is wrong in my internal portion of the while loop I think...