current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to get the output of queries within the with clause of my final query as csv or some sort of text files. I only have query access, I'm not allowed to create tables for this database. I have a set of queries that do some calculations on a data set, another set of queries that compute on the previous set and yet another that calculates on the final set. I don't want to run all of it as three seperate queries because the results from the first two are actually in the last one. 

WITH
    Q1 AS(
        SELECT col1, col2, col3, col4, col5, col6, col7
        FROM table1
    ),
    Q2 AS(
        SELECT AVG(col1) as col1Avg, MAX(col1) as col1Max, col2, col3,col4
        FROm Q1
        GROUP BY col2, col3, col4
    )
SELECT 
    AVG(col1AVG), col3
FROM
    Q2
GROUP BY col3

I would like the results from Q1, Q2 and the final select statement as preferably 3 csv files but I could live with all of it in one csv file. Is this possible?

Thanks!

Edit: Just to clarify, the columns from the queries are very different. I'm definitely pulling more columns from my first query than my second. I've edited the above code a bit to make this more clear.

share|improve this question
    
Typically a csv file would have the same number of columns for each row. –  David Aldridge May 10 '13 at 14:25

1 Answer 1

up vote 1 down vote accepted

To combine all the results together you'd use UNION ALL, but the number and data types of the columns must match.

select col1, col2, col2
from   blah
union all
select col1, col2, col2
from   blah2
union all
... etc

You can reference CTE's in there of course ...

with
  cte_1 as (
    select ... from ...),
  cte_2 as (
    select ... from ... cte_1),
  cte_3 as (
    select ... from ... cte_2)
select col1, col2, col2
from   cte_1
union all
select col1, col2, col2
from   cte_2
union all
select col1, col2, col2
from   cte_3

If your final output is a csv then it looks like you have multiple row formats in there -- checksums? If so, in the queries that you union all together you might like to combine all the columns from each query into one string ...

with
  cte_1 as (
    select ... from ...),
  cte_2 as (
    select ... from ... cte_1),
  cte_3 as (
    select ... from ... cte_2)
select col1||','||col2||','||col2
from   cte_1
union all
select col1||','||col2
from   cte_2
union all
select col1
from   cte_3
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.