Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: 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

    Sam
    Last edited by pokemink; 03-11-04 at 12:40.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The 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...

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What?

    You don't like it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2003
    Posts
    3
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •