SQL problems merging queries

[Stevenn]

I print data from an Oracle database with adodb in Excel 2007.

I have problems merging two sql queries. Now I use

SELECT AMOUNT FROM TABLE_NAME WHERE BUDGET_ID = 1

and

SELECT AMOUNT FROM TABLE_NAME WHERE BUDGET_ID = 20

and want to merge these, so the recordset contains two columns "amount_1" and "amount_24". I guess I need to make two queries and naming these somehow.

 

[Colin Legg]

Okay - here's the SQL Server query. I hope it works for you in Oracle.

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL 
    DROP TABLE #T
        
CREATE TABLE #T
    (
      [Project_Number] INT ,
      Budget_ID INT ,
      [Month_Number] INT ,
      [Amount] FLOAT
    )
INSERT  INTO #T
        ( [Project_Number], [Budget_ID], [Month_Number], [Amount] )
VALUES  ( 1, 1, 3, 800 ),
        ( 1, 1, 4, 700 ),
        ( 1, 1, 5, 600 ),
        ( 1, 24, 2, 500 ),
        ( 1, 24, 3, 800 ),
        ( 1, 24, 4, 700 ),
        ( 1, 24, 5, 700 ),
        ( 1, 24, 1, 600 ),
        ( 1, 24, 12, 400 ),
        ( 1, 24, 12, 500 )

SELECT  T1.*
FROM    ( SELECT    Project_Number ,
                    CASE Budget_ID
                      WHEN 1 THEN 'Budget'
                      WHEN 24 THEN 'Forecast'
                    END + '_' + LEFT(DATENAME(Month,
                                              DATEADD(Month, Month_Number - 1,
                                                      '1900-01-01')), 3) AS Month_Name ,
                    SUM(Amount) AS Amount
          FROM      #T
          WHERE     Budget_ID IN ( 1, 24 )
          GROUP BY  Project_Number ,
                    CASE Budget_ID
                      WHEN 1 THEN 'Budget'
                      WHEN 24 THEN 'Forecast'
                    END + '_' + LEFT(DATENAME(Month,
                                              DATEADD(Month, Month_Number - 1,
                                                      '1900-01-01')), 3)
        ) AS T PIVOT ( SUM(Amount) FOR Month_Name IN ( [Budget_Jan],
                                                       [Budget_Feb],
                                                       [Budget_Mar],
                                                       [Budget_Apr],
                                                       [Budget_May],
                                                       [Budget_Jun],
                                                       [Budget_Jul],
                                                       [Budget_Aug],
                                                       [Budget_Sep],
                                                       [Budget_Oct],
                                                       [Budget_Nov],
                                                       [Budget_Dec],
                                                       [Forecast_Jan],
                                                       [Forecast_Feb],
                                                       [Forecast_Mar],
                                                       [Forecast_Apr],
                                                       [Forecast_May],
                                                       [Forecast_Jun],
                                                       [Forecast_Jul],
                                                       [Forecast_Aug],
                                                       [Forecast_Sep],
                                                       [Forecast_Oct],
                                                       [Forecast_Nov],
                                                       [Forecast_Dec] ) ) AS T1

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL 
    DROP TABLE #T

 

[Stevenn]

Okay - here's the SQL Server query. I hope it works for you in Oracle.

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL 
    DROP TABLE #T
 
CREATE TABLE #T
    (
      [Project_Number] INT ,
      Budget_ID INT ,
      [Month_Number] INT ,
      [Amount] FLOAT
    )
INSERT  INTO #T
        ( [Project_Number], [Budget_ID], [Month_Number], [Amount] )
VALUES  ( 1, 1, 3, 800 ),
        ( 1, 1, 4, 700 ),
        ( 1, 1, 5, 600 ),
        ( 1, 24, 2, 500 ),
        ( 1, 24, 3, 800 ),
        ( 1, 24, 4, 700 ),
        ( 1, 24, 5, 700 ),
        ( 1, 24, 1, 600 ),
        ( 1, 24, 12, 400 ),
        ( 1, 24, 12, 500 )
 
SELECT  T1.*
FROM    ( SELECT    Project_Number ,
                    CASE Budget_ID
                      WHEN 1 THEN 'Budget'
                      WHEN 24 THEN 'Forecast'
                    END + '_' + LEFT(DATENAME(Month,
                                              DATEADD(Month, Month_Number - 1,
                                                      '1900-01-01')), 3) AS Month_Name ,
                    SUM(Amount) AS Amount
          FROM      #T
          WHERE     Budget_ID IN ( 1, 24 )
          GROUP BY  Project_Number ,
                    CASE Budget_ID
                      WHEN 1 THEN 'Budget'
                      WHEN 24 THEN 'Forecast'
                    END + '_' + LEFT(DATENAME(Month,
                                              DATEADD(Month, Month_Number - 1,
                                                      '1900-01-01')), 3)
        ) AS T PIVOT ( SUM(Amount) FOR Month_Name IN ( [Budget_Jan],
                                                       [Budget_Feb],
                                                       [Budget_Mar],
                                                       [Budget_Apr],
                                                       [Budget_May],
                                                       [Budget_Jun],
                                                       [Budget_Jul],
                                                       [Budget_Aug],
                                                       [Budget_Sep],
                                                       [Budget_Oct],
                                                       [Budget_Nov],
                                                       [Budget_Dec],
                                                       [Forecast_Jan],
                                                       [Forecast_Feb],
                                                       [Forecast_Mar],
                                                       [Forecast_Apr],
                                                       [Forecast_May],
                                                       [Forecast_Jun],
                                                       [Forecast_Jul],
                                                       [Forecast_Aug],
                                                       [Forecast_Sep],
                                                       [Forecast_Oct],
                                                       [Forecast_Nov],
                                                       [Forecast_Dec] ) ) AS T1
 
IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL 
    DROP TABLE #T

Hi Colin Legg,

It works just great

Thank you. It was a great help.