SQL problems merging queries

Stevenn · Mar 9, 2012

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

I have problems merging two sql queries. Now I use

Code:

SELECT AMOUNT FROM TABLE_NAME WHERE BUDGET_ID = 1

and

Code:

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.

Stevenn · Mar 12, 2012

Firefly2012 said:

Do you mean like this (using Colin's SQL):

Code:

SELECT Project_Number, MonthNumber,
   SUM(CASE WHEN Budget_ID = 1 THEN Amount ELSE 0 END) AS Amount_1,
   SUM(CASE WHEN Budget_ID = 20 THEN Amount ELSE 0 END) AS Amount_24 
FROM TABLE_NAME
WHERE Budget_ID IN (1,20)
GROUP BY Project_Number, MonthNumber

No. Instead of having them grouped by project_number and month_number I want the data for each month in columns only grouped by project_number.

Exactly like this

Code:

SUM(CASE WHEN BUDGET_ID = 0 AND MONTH = 1 THEN AMOUNT ELSE 0 END) AS AMOUNT_0_JANUARY,
...,
SUM(CASE WHEN BUDGET_ID = 0 AND MONTH = 12 THEN AMOUNT ELSE 0 END) AS AMOUNT_0_DECEMBER,
SUM(CASE WHEN BUDGET_ID = 24 AND MONTH = 1 THEN AMOUNT ELSE 0 END) AS AMOUNT_24_JANUARY,
...,
SUM(CASE WHEN BUDGET_ID = 24 AND MONTH = 12 THEN AMOUNT ELSE 0 END) AS AMOUNT_24_DECEMBER

The above code gives me a total of 24 lines, and I guess it's possible to do something tricky to cut off some of the lines, but getting the same result.

Colin Legg · Mar 12, 2012

Stevenn said:
I guess it will be a problem to use pivot tables because I'm going to upload the data after I've retrieved the data.

I don't mean pivot tables, I mean pivot it in the SQL.

For example, it really sounds to me like you want something like the below. I'm just creating a temporary table for testing purposes so I have something to work with. The bit of code from SELECT T1.* is the part where I'm getting data from the table.

Code:

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL 
        DROP TABLE #T
 
CREATE TABLE #T ([ID] INT ,[Project_Number] INT, Amount FLOAT,Budget_ID INT, [Business_Date] DATETIME)
INSERT INTO #T ( [ID]  ,[Project_Number] , [Amount] , [Budget_ID], [Business_Date] )
VALUES  
(1,1,312,1,'20120101'),
(2,1,400,1,'20120205'),
(3,1,900,20,'20120115'),
(4,1,812,20,'20120118'),
(5,1,1800,20,'20120322'),
(6,1,40,1,'20120322')
 
SELECT T1.* 
FROM
 (
 SELECT 
  Project_Number, 
  LEFT(DATENAME(m,Business_Date),3) AS Month_Name, 
  Budget_ID,
  SUM(Amount) AS Amount
 FROM #T
 WHERE Budget_ID IN (1,20)
  AND YEAR(Business_Date)=2012 --I only want 2012 dates
 GROUP BY
  Project_Number, 
  DATENAME(m,Business_Date),
  Budget_ID
 HAVING SUM(Amount)<>0
 ) AS T
PIVOT (SUM(Amount) FOR Month_Name IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS T1

The output looks like this:

Code:

Project_Number  Budget_ID   Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
1               1           312  400  40   NULL NULL NULL NULL NULL NULL NULL NULL NULL
1               20          1712 NULL 1800 NULL NULL NULL NULL NULL NULL NULL NULL NULL

So what I'm doing there is pivoting by the months rather than by the Budget_ID, which makes more sense to me. As I mentioned, I've done this in SQL Server - I don't have any experience with Oracle.

Stevenn · Mar 12, 2012

Colin Legg said:
I don't mean pivot tables, I mean pivot it in the SQL.

For example, it really sounds to me like you want something like the below. I'm just creating a temporary table for testing purposes so I have something to work with. The bit of code from SELECT T1.* is the part where I'm getting data from the table.

Code:

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL DROP TABLE #T CREATE TABLE #T ([ID] INT ,[Project_Number] INT, Amount FLOAT,Budget_ID INT, [Business_Date] DATETIME) INSERT INTO #T ( [ID] ,[Project_Number] , [Amount] , [Budget_ID], [Business_Date] ) VALUES (1,1,312,1,'20120101'), (2,1,400,1,'20120205'), (3,1,900,20,'20120115'), (4,1,812,20,'20120118'), (5,1,1800,20,'20120322'), (5,1,40,1,'20120322') SELECT T1.* FROM ( SELECT Project_Number, LEFT(DATENAME(m,Business_Date),3) AS Month_Name, Budget_ID, SUM(Amount) AS Amount FROM #T WHERE Budget_ID IN (1,20) AND YEAR(Business_Date)=2012 --I only want 2012 dates GROUP BY Project_Number, DATENAME(m,Business_Date), Budget_ID HAVING SUM(Amount)<>0 ) AS T PIVOT (SUM(Amount) FOR Month_Name IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS T1

The output looks like this:

Code:

Project_Number Budget_ID Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 1 1 312 400 40 NULL NULL NULL NULL NULL NULL NULL NULL NULL 1 20 1712 NULL 1800 NULL NULL NULL NULL NULL NULL NULL NULL NULL

So what I'm doing there is pivoting by the months rather than by the Budget_ID, which makes more sense to me. As I mentioned, I've done this in SQL Server - I don't have any experience with Oracle.

Oh. It's way more advanced SQL than I've ever worked with. It looks really good, but based on your great example I need a total of 26 columns instead of 14 columns. Instead of the names Jan, Feb, ..., Dec I need Jan_1, Feb_1, ..., Dec_1, Jan_20, Feb_20, ..., Dec_20. I know it gives a messy layout, but it's what i need. I've absolutely no experience with SQL-pivoting.

Colin Legg · Mar 12, 2012

EDIT: Sorry I missed this:

I know it gives a messy layout, but it's what i need.

I think it'd be:

Code:

SELECT T1.* 
FROM
 (
 SELECT 
  Project_Number, 
  LEFT(DATENAME(m,Business_Date),3) + '_' + CAST(Budget_ID AS VARCHAR) AS Month_Name, 
  Budget_ID,
  SUM(Amount) AS Amount
 FROM #T
 WHERE Budget_ID IN (1,20)
  AND YEAR(Business_Date)=2012 --I only want 2012 dates
 GROUP BY
  Project_Number, 
  DATENAME(m,Business_Date),
  Budget_ID
 HAVING SUM(Amount)<>0
 ) AS T
PIVOT (SUM(Amount) FOR Month_Name IN (
 [Jan_1],[Feb_1],[Mar_1],[Apr_1],[May_1],[Jun_1],[Jul_1],[Aug_1],[Sep_1],[Oct_1],[Nov_1],[Dec_1],
 [Jan_20],[Feb_20],[Mar_20],[Apr_20],[May_20],[Jun_20],[Jul_20],[Aug_20],[Sep_20],[Oct_20],[Nov_20],[Dec_20])) AS T1

But it's getting pretty messy.

Colin Legg · Mar 12, 2012

Correction -

Code:

SELECT T1.* 
FROM
 (
 SELECT 
  Project_Number, 
  LEFT(DATENAME(m,Business_Date),3) + '_' + CAST(Budget_ID AS VARCHAR) AS Month_Name, 
  SUM(Amount) AS Amount
 FROM #T
 WHERE Budget_ID IN (1,20)
  AND YEAR(Business_Date)=2012 --I only want 2012 dates
 GROUP BY
  Project_Number, 
  Business_Date,
  LEFT(DATENAME(m,Business_Date),3) + '_' + CAST(Budget_ID AS VARCHAR)
 HAVING SUM(Amount)<>0
 ) AS T
PIVOT (SUM(Amount) FOR Month_Name IN (
 [Jan_1],[Feb_1],[Mar_1],[Apr_1],[May_1],[Jun_1],[Jul_1],[Aug_1],[Sep_1],[Oct_1],[Nov_1],[Dec_1],
 [Jan_20],[Feb_20],[Mar_20],[Apr_20],[May_20],[Jun_20],[Jul_20],[Aug_20],[Sep_20],[Oct_20],[Nov_20],[Dec_20])) AS T1

Stevenn · Mar 12, 2012

Colin Legg said:

Correction -

Code:

SELECT T1.* 
FROM
 (
 SELECT 
  Project_Number, 
  LEFT(DATENAME(m,Business_Date),3) + '_' + CAST(Budget_ID AS VARCHAR) AS Month_Name, 
  SUM(Amount) AS Amount
 FROM #T
 WHERE Budget_ID IN (1,20)
  AND YEAR(Business_Date)=2012 --I only want 2012 dates
 GROUP BY
  Project_Number, 
  Business_Date,
  LEFT(DATENAME(m,Business_Date),3) + '_' + CAST(Budget_ID AS VARCHAR)
 HAVING SUM(Amount)<>0
 ) AS T
PIVOT (SUM(Amount) FOR Month_Name IN (
 [Jan_1],[Feb_1],[Mar_1],[Apr_1],[May_1],[Jun_1],[Jul_1],[Aug_1],[Sep_1],[Oct_1],[Nov_1],[Dec_1],
 [Jan_20],[Feb_20],[Mar_20],[Apr_20],[May_20],[Jun_20],[Jul_20],[Aug_20],[Sep_20],[Oct_20],[Nov_20],[Dec_20])) AS T1

Hi Colin Legg,

I've really tried to work further with the code. What if I don't have any [Business_Date] in my code and I want the two kind of budget numbers to be called "BUDGET_MonthName" and "FORECAST_MonthName"?

Colin Legg · Mar 12, 2012

I'll try to help. Could you give me an example table with say 10 records, and then show me exactly how you want the output to be?

Stevenn · Mar 12, 2012

Colin Legg said:
I'll try to help. Could you give me an example table with say 10 records, and then show me exactly how you want the output to be?

==
Project_number, Budget_id, Month_number, Amount
----
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
==

Output:
Project_number, Budget_Jan, Budget_Feb, ..., Budget_Dec, Forecast_Jan, Forecast_Feb, ..., Forecast_Dec
-----
1, SUM Amount WHERE Budget_id = 1 and Month_number = 1, SUM Amount WHERE Budget_id = 1 and Month_number = 2, ..., SUM Amount WHERE Budget_id = 1 and Month_number = 12, SUM Amount WHERE Budget_id = 24 and Month_number = 1, SUM Amount WHERE Budget_id = 24 and Month_number = 2, ..., SUM Amount WHERE Budget_id = 24 and Month_number = 12

So based on the data example above I only want a row for each project_number and a lot of columns. Eventually also a BUDGET_TOTAL and FORECAST_TOTAL and a difference, but I guess and hope I can make that myself

Colin Legg · Mar 12, 2012

So the column header will be Budget_xxx when the Budget_ID is 1 and the column header will be Forecast_xxx when the Budget_ID is 24?

Stevenn · Mar 12, 2012

Colin Legg said:
So the column header will be Budget_xxx when the Budget_ID is 1 and the column header will be Forecast_xxx when the Budget_ID is 24?

Yes exactly

SQL problems merging queries

Stevenn

Active Member

Stevenn

Active Member

Excel Facts

Colin Legg

MrExcel MVP

Stevenn

Active Member

Colin Legg

MrExcel MVP

Colin Legg

MrExcel MVP

Stevenn

Active Member

Colin Legg

MrExcel MVP

Stevenn

Active Member

Colin Legg

MrExcel MVP

Stevenn

Active Member

Similar threads

Forum statistics

Share this page

SQL problems merging queries

Active Member

Active Member

Excel Facts

MrExcel MVP

Active Member

MrExcel MVP

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock