Changing table format

Hi there,

Its probably easier to draw this problem than describe it, so here goes:

I have a sales forecast table (A,B,C are products [PRODUCT], the dates refer to the month for which the forecast is [FORECAST_DATE], and the integer is the forecast sales qty [FORECAST_QTY])

A 01/03/2004 30
B 01/03/2004 28
C 01/03/2004 24
A 01/04/2004 11
B 01/04/2004 09
C 01/04/2004 41

I need to convert the table into a more sensible format, like this:

(NB ...Dots are just there to help with formatting - basically I'm talking about a field for FORECAST_03_2004, FORECAST_04_2004 etc etc)

...... 01/03/2004...01/04/2004
A........30..............11
B........28..............09
C........24..............41

I'm really no t-SQL guru, and I can't seem to get any joy out of BOL. It must only be a tiny bit of code. Can anyone help?

Thanks very much

SamThe picture is nice, but DDL, DML and sample data is better.

Notice I added an additional data row for 'A' of rthe third...

Just cut and paste in to Query Analyzer and execute it...should run without a hitch..

Let me know how it works for you...

USE Northwind
GO

CREATE TABLE myTable99(Product char(1), Forecast_Date datetime, Forecast_Qty int)
GO

INSERT INTO myTable99(Product, Forecast_Date, Forecast_Qty)
SELECT 'A', '01/03/2004', 30 UNION ALL
SELECT 'B', '01/03/2004', 28 UNION ALL
SELECT 'C', '01/03/2004', 24 UNION ALL
SELECT 'A', '01/03/2004', 10 UNION ALL
SELECT 'A', '01/04/2004', 11 UNION ALL
SELECT 'B', '01/04/2004', 09 UNION ALL
SELECT 'C', '01/04/2004', 41
GO

DECLARE @.SQL varchar(8000)
, @.SQL1 varchar(100)
, @.SQL2 varchar(100)
, @.SQL3 varchar(100)
, @.SQL4 varchar(100)
, @.NewCol char(10)

SELECT @.SQL1 = 'SELECT Product, '
, @.SQL2 = ' SUM(CASE WHEN Forecast_Date = ' + ''''
, @.SQL3 = '''' + 'THEN Forecast_QTY ELSE 0 END) AS ['
, @.SQL4 = ' FROM MyTable99 GROUP BY Product'

SELECT @.SQL = @.SQL1

DECLARE myCursor99 CURSOR
FOR
SELECT DISTINCT CONVERT(CHAR(10),Forecast_Date,101) FROM myTable99

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @.NewCol

WHILE @.@.FETCH_STATUS = 0

BEGIN
SELECT @.SQL = @.SQL + @.SQL2 + @.NewCol + @.SQL3 + @.NewCol + '],'
FETCH NEXT FROM myCursor99 INTO @.NewCol
END

CLOSE myCursor99
DEALLOCATE myCursor99

SELECT @.SQL = LEFT(@.SQL,LEN(@.SQL)-1) + @.SQL4

SELECT @.SQL

EXEC(@.SQL)
GO

DROP TABLE myTable99
GO|||What?

You don't like it?|||Sorry mate,

I've just this second got back to the office.

I'm impressed already, just LOOKING at the code ;-)

Thanks very much. I'll give it a run out today, and let you know.

Thanks in advance for your very comprehensive reply.

Sam|||Originally posted by pokemink
I've just this second got back to the office.


On holiday I hope...

Just cut and paste the code in to QA, and it should run...