0

I have a web page I created in PHP that has seven links - each link downloads a .CSV file with results from a different Oracle SQL query. The user would like to know if they can all be in one .CSV file, with the results of each query on its own sheet within the same workbook. I am assuming this is not possible, but didn't want to say no without at least looking into it. Have tried searching for the answer online, but haven't found anything close.

To give you an example, here are two of the queries used currently. They would want the results of each query on its own sheet within the same workbook/csv file.

SELECT StoreNbr, 
SUM(CASE WHEN(OrigFormMethod = 'FORMULA BK') THEN Gallons ELSE 0 END) AS Formula_Bk_Gals,
SUM(CASE WHEN(OrigFormMethod = 'MANUAL') THEN Gallons ELSE 0 END) AS Manual_Gals,
SUM(CASE WHEN(OrigFormMethod = 'PCT OF SC') THEN Gallons ELSE 0 END) AS Pct_Of_Sc_Gals,
SUM(CASE WHEN(OrigFormMethod = 'MODIFIED PCT OF SC') THEN Gallons ELSE 0 END) AS Modified_Pct_Of_Sc_Gals,
SUM(CASE WHEN(OrigFormMethod = 'SHER-COLOR') THEN Gallons ELSE 0 END) AS SherColor_Gals,
SUM(CASE WHEN(OrigFormMethod = 'MODIFIED SHER-COLOR') THEN Gallons ELSE 0 END) AS Modified_SherColor_Gals,
SUM(CASE WHEN(OrigFormMethod = 'MODIFIED FORMULA BK') THEN Gallons ELSE 0 END) AS Modified_Formula_Bk,
SUM(CASE WHEN(OrigFormMethod = 'PCT OF FB') THEN Gallons ELSE 0 END) AS Pct_Of_Formula_Bk, 
SUM(CASE WHEN(OrigFormMethod = 'MODIFIED PCT OF FB') THEN Gallons ELSE 0 END) AS Modified_Pct_Of_Formula_Bk
FROM MQ_CDS_NETTRAN
WHERE ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10') 
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
AND ClrntSys = 'GIC'
GROUP BY StoreNbr
ORDER BY StoreNbr;

SELECT 
StoreNbr, 
TRUNC(LastTranDate, 'DAY') AS Week_Beginning,
SUM(CASE WHEN(ClrntSys IN ('844', '84448')) THEN Gallons ELSE 0 END) AS Gallons_844,
SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) AS Gallons_GIC,
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE 0 END) AS Total_Gallons,
CONCAT(CAST((SUM(CASE WHEN(ClrntSys ='GIC') THEN Gallons ELSE 0 END) /
SUM(CASE WHEN(ClrntSys IN ('844', '84448', 'GIC')) THEN Gallons ELSE NULL END)) AS DECIMAL (7,2)) * 100, '%') AS Percent_GIC
FROM MQ_CDS_NETTRAN
WHERE ((CostCenter = '701104' AND LastTranDate >= DATE '2020-03-10')
OR (CostCenter = '701109' AND LastTranDate >= DATE '2020-03-04')
OR (CostCenter = '701257' AND LastTranDate >= DATE '2020-03-12'))
GROUP BY ROLLUP(StoreNbr), TRUNC(LastTranDate, 'DAY')
ORDER BY TRUNC(LastTranDate, 'DAY'), StoreNbr;

Is this possible?

BigRedEO
  • 807
  • 4
  • 13
  • 33
  • they want an Excel file with multiple queries going to their own worksheets...? – thatjeffsmith Jul 06 '20 at 15:10
  • 1
    CSV files are not suited to having a change in the number of columns (and their headings) part of the way through. If someone gave me a CSV file like that, I'd give it back to them, with some choice words added for good measure. – MatBailie Jul 06 '20 at 15:12
  • 4
    CSV files have no concept of worksheets, it is just a list of data in a particular format. The options would be to produce multiple CSV's and zip them together or as pointed out to produce a spreadsheet (https://stackoverflow.com/questions/3269345/how-to-generate-an-excel-document-with-multiple-worksheets-from-php) – Nigel Ren Jul 06 '20 at 15:12
  • @NigelRen This might work for them. I will look into this to see if I can make it work. Thank you. – BigRedEO Jul 06 '20 at 15:17
  • You could use [PHPSpreadSheet](https://github.com/PHPOffice/PhpSpreadsheet) to create Excel files with multiple worksheets but, as has been pointed out, there is no such thing in simple CSV files. – Dave Jul 06 '20 at 16:27

0 Answers0